topic Re: Nested IF statement with set analysis doesn't work in QlikView

Nested IF statement with set analysis doesn't work

_Karen_ — Sat, 16 Nov 2024 04:06:30 GMT

Dear all,

I am having trouble with a nested IF statement with set analysis. Please help!

I have Change Request data that is read from ten separate databases. Each database has the same structure, but in one of the databases the field I am interested in for this table is called something different.

So each Change Log request corresponds to a ResultID and each ResultID has several fields.

For nine of the databases, the fields I'm interested in have the Description "ChangeLog" and "ChangeLog Complete" and for the tenth, the corresponding fields are "Change Log" and "Complete?" , and it has an extra field which is "Accepted?"

I have a variable called vComplete and when I change it, I want the table to show all the Change Requests where "Complete?" or "ChangeLog Complete" match the variable value, but also all the Change Requests where "Complete?" or "ChangeLog Complete" are null.

I've created an example which simulates what i'm doing (attached). In the example, the table titled "IF Statement #2 only" is behaving as expected, but as soon as I try to combine the IF statements into one table - "Combined IF Statements", it goes wrong.

What am I doing wrong?

Any help gratefully received. Thank you in advance.

Karen

Re: Nested IF statement with set analysis doesn't work

sunny_talwar — Wed, 20 Mar 2019 12:44:13 GMT

What do you want the "combined if statements" table to look like? I mean what isn't right?

Re: Nested IF statement with set analysis doesn't work

_Karen_ — Wed, 20 Mar 2019 13:04:31 GMT

Hi Sunny,

Thank you for looking at this.

When vComplete = "Yes", I want the table to look like Yes.gif (attached)

When vComplete = "No", I want the table to look like No.gif (also attached)

So it's filtering on "Complete?" and "ChangeLog Complete" but also showing the rows where those values are null.

Does that make sense?

Best wishes,

KAren

Re: Nested IF statement with set analysis doesn't work

_Karen_ — Thu, 21 Mar 2019 09:25:02 GMT

I've solved it!

Apart from the mistake in the inline load where I had 'Machine QC' instead of 'Monthly QC', I realised that this part of the first IF statement:

Count({$-<Description={'ChangeLog Complete'},StringValue={'*'},ProcedureName={'Monthly QC'}>}DateTime)

always returned 1 because if ChangeLog Complete didn't exist, then the number of valid StringValues was 0.

So the second nested IF was never used.

I've revised the IF statement to be

=if(Count({$<Description={'ChangeLog Complete'},StringValue={$(vComplete)},ProcedureName={'Monthly QC'}>}DateTime)
+ Count({$-<Description={'ChangeLog Complete'},StringValue={'*'},ProcedureName={'Monthly QC'}>}DateTime)
+ Count({$<Description={'Complete?'},StringValue={$(vComplete)},ProcedureName={'Change Log'}>}DateTime)
+ count({$-<Description={'Complete?'},StringValue={'*'},ProcedureName={'Change Log'}>}DateTime)>1,

if(count({$<Description={'ChangeLog Complete'}>}
DateTime)>0,
avg({$<Description={'ChangeLog Complete'}>}
DateTime),

avg({$<Description={'Complete?'}>}
DateTime)

))

Can't imagine this will help anyone else, but might as well upload the working file for completeness.

Thank you Sunny for looking at this.