topic If Else statement into Set Analysis in QlikView

If Else statement into Set Analysis

Fri, 05 Nov 2010 20:13:23 GMT

Hello,

How can I rewrite the following If Else statement using Set Analysis?

I am trying to display the variables [ABC admin target], [ABC client target], and [ABC internal target] (user entered input in an Input Box) next to the according employee/project category in a straight table.

I got the first part to work by doing:

sum({$<[Last Name] ={"ABC"}, [Project Code Type]={"Admin"}>} [ABC admin target] )

but I am not sure how I can write the Else statement using Set Analysis.

Here's the If Else Statement:

if([Last Name]='ABC' and [Project Code Type]='Admin',[ABC admin target],

if([Last Name]='ABC' and [Project Code Type]='Client',[ABC client target],

if([Last Name]='ABC' and [Project Code Type]='Internal',[ABC internal target]))

Thanks, Maria

If Else statement into Set Analysis

johnw — Fri, 05 Nov 2010 20:22:44 GMT

Maybe do the same for the others and add them together? I also recommend single quotes instead of double quotes for literals. Double quotes specify a search string, which while it returns the exact same results, might take a different and slightly less efficient path through the system. So single quotes for literals, double quotes for search strings.

sum({<[Last Name]={'ABC'},[Project Code Type]={'Admin'} >} [ABC admin target])
+sum({<[Last Name]={'ABC'},[Project Code Type]={'Client'} >} [ABC client target])
+sum({<[Last Name]={'ABC'},[Project Code Type]={'Internal'}>} [ABC internal target])

However, if those are the ONLY three project code types, this might be just as efficient, possibly even better (on the down side, to me, the above is more clear):

sum({<[Last Name]={'ABC'}>}
pick(match([Project Code Type],'Admin','Client','Internal')
,[ABC admin target],[ABC client target],[ABC internal target]))

If Else statement into Set Analysis

Fri, 05 Nov 2010 20:50:46 GMT

Actually, I did try that, but it doesn't work. I think it may have to do with the fact that I don't actually need to sum up these variables, but in order to use Set Analysis, I need to do some kind of summation or aggregation, right?

Maria

If Else statement into Set Analysis

johnw — Fri, 05 Nov 2010 21:09:37 GMT

If you don't want an aggregation, then you don't want set analysis. You COULD use only() where I used sum() to force it to work, but I think it will actually be less efficient than what you started with. I'd stick with your nested if(), or replace it with this less clear but more compact version:

if([Last Name]='ABC'
,pick(match([Project Code Type],'Admin','Client','Internal')
,[ABC admin target],[ABC client target],[ABC internal target]))