Conditional Check on a One-Many Relationship

[Anonymous]

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

[timpoismans]

Hi Manikantan

Please try the following measure:

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

(Dummy data on top, solution at the bottom)

Hope this helps.

Regards,

Tim P.

[juraj_misina]

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% )

[Anonymous]

Hi Juraj, Max doesnt work in expression.

[Anonymous]

Thanks Tim. Logical solution!!

[timpoismans · Accepted solution]

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.

[Anonymous]

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

[timpoismans]

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.:

ProjectID	CompletionPercentage
A	0
A	15
A	27
A	39
B	0
B	10
B	34
B	78
B	100

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

[Anonymous]

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).

[timpoismans]

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

ProjectID	CompletionPercentage
A	0
A	15
A	27
A	39
B	0
B	10
B	34
B	50
B	78
B	100

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)