11 Replies Latest reply: Sep 28, 2018 5:35 AM by Tim Poismans

Conditional Check on a One-Many Relationship

Hi All,

I have 2 tables with One - Many Relation ship.

Table 1 - Contains ProjectID, one row per Project ID

Table 2 - Contains Project ID, Multiple rows per ProjectID with different values of completion % i.e a project ID will have multiple rows with different values of Completion %.

In a table in Qlik Sense, i create a column called 'Completed' to identify if a project is completed or not i.e. if for a Project ID, there is a row with completion % 100, it is complet.

=If(CompletionPercentage = 100, 'Y', 'N')

If i use the above expression i get 2 rows for each Project ID, one with value Y and one with value N.

I understand that each row in the second table is being checked.

Is there any other way to achieve this requirement?

Yours Truly,

S.Manikantan

• Re: Conditional Check on a One-Many Relationship

Hi Manikantan

If(Sum(CompletionPercentage)/Count(ProjectID)<100,'N','Y')

(Dummy data on top, solution at the bottom)

Hope this helps.

Regards,

Tim P.

• Re: Conditional Check on a One-Many Relationship

Thanks Tim. Logical solution!!

• Re: Conditional Check on a One-Many Relationship

Optionally you can use

=If(Max(CompletionPercentage) = 100, 'Y', 'N')

to only check last available value (I assume there aren't any projects completed to more then 100% )

• Re: Conditional Check on a One-Many Relationship

Hi Juraj, Max doesnt work in expression.

• Re: Conditional Check on a One-Many Relationship

Hi Manikantan

Max does work in expressions, but you need at least an Aggr expression to make it work:

If(Aggr(Max(CompletionPercentage),ProjectID)=100,'Y','N')

But the danger with Max might be when a project hits 100, but issues arise and it gets a setback to 97, it will still take the 100:

and thus show the project as completed as it might not be.

Don't know if this could be an issue or not.

Regards,

Tim P.

• Re: Conditional Check on a One-Many Relationship

Thanks Tim. Thats Perfect for my requirement.

Just to understand, i can use this kind of Aggregations only when i want to find a Max (or Min or Avg). Is it possible to check if atleast one row for the project has a completion percentage of exactly 50% (not greater or lesser)?

Yours Truly,

S.Manikantan

• Re: Conditional Check on a One-Many Relationship

Just so I can understand your datamodel a bit better:

When you say a ProjectID can have multiple values of CompletionPercentage, is this a growing percentage?

E.g.:

ProjectIDCompletionPercentage
A0
A15
A27
A39
B

0

B

10

B34
B

78

B100

So, Project A is at 39% completion and Project B is at 100% completion?

• Re: Conditional Check on a One-Many Relationship

That's Correct Tim. So now using your suggestion above i can find if any project has crossed 50% completion using condition that Aggr(Max) > 50. But if i want to know if a project has touched exact 50% at some point of time. Max or Min etc cannnot be used. Is this feasible? (Just a hypothetical question to understand if we can accomplish this in a one-many relationship. Actually, my requirement has been met with the above answer).

• Re: Conditional Check on a One-Many Relationship

I modified the dummy data from my previous answer to include a 50% at some point:

ProjectIDCompletionPercentage
A0
A15
A27
A39
B

0

B

10

B34
B

50

B78
B100

Now if you'd use the following measure:

If(Aggr(Count({<CompletionPercentage={50}>}CompletionPercentage),ProjectID)>=1,'Y','N')

This will allow you to check if any ProjectID has at least hit exactly 50% one time. The measure allows for a ProjectID to hit 50% multiple times (e.g. you'd keep record of the CompletionPercentage by date and a Project would be stuck at 50% for multiple dates)

• Re: Conditional Check on a One-Many Relationship

Wow!! Excellent one Tim.

• Re: Conditional Check on a One-Many Relationship

Happy to help!