Announcements
cancel
Showing results for
Did you mean:
Creator III

## Why two if statements with a variable condition need sa aggr() to work out?

Hello,

I always wondering why two conditions outcomes with a variable needs a aggr() to work.

I try to make an easy example.I have following case:

Given is may variable vFormula='1'

if(if(vFormula='1', count({\$<Type='Red'>}, count({\$<Type='green'>}),Ordernumber)

So basically when variable vFormula is '1' show me the ordernumbers where type is red, and if its '0' then show me those ordernumbers where type is green. I find it makes sense but seemingly no returns.

Only if if put in additional aggr() and a boolean query like >0 it seems to work right for me:

aggr(if(if(vFormula='1', count({\$<Type='Red'>}, count({\$<Type='green'>}),Ordernumber)>0), Ordernumber)

Why the first form wouldn't produce results like the last one?

Thank you very much in advance.

Best.

1 Solution

Accepted Solutions

Could be because you are using this as dimenion? If it is dimension Qlik should understand the functionlity of Aggregare of dimension on what you are preferring?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
4 Replies

For me your conditional syntax is wrong. Perhaps you could try this way

If(vFormula='1', count({\$<Type={'Red'}>} Ordernumber), count({\$<Type={'green'}>} Ordernumber)) // This condition will work when you have Only 1 and 0

If(vFormula='1', count({\$<Type={'Red'}>} Ordernumber), If(vFormula='0', count({\$<Type={'green'}>} Ordernumber))) // This condition will work when you have 0,1,2,3,...

Pick(Match(\$(vFormula), '1', '0'),  count({\$<Type={'Red'}>} Ordernumber), count({\$<Type={'green'}>} Ordernumber)) // But, I would go this way to make more efficient

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Creator III
Author

Hello @Anil_Babu_Samineni ,

I found the mistake in my syntax. I misplaced the '>0' value.

Old:

aggr(if(if(vFormula='1', count({\$<Type='Red'>}, count({\$<Type='green'>}),Ordernumber)>0), Ordernumber)

Of course thats the right one, I made a typo:

aggr(if(if(vFormula='1', count({\$<Type='Red'>}, count({\$<Type='green'>})>0,Ordernumber)), Ordernumber)

That corrected one give me the dimension with the given condition. And back to my question:

When I take away the aggr() why this gives me no returns?I get null values only but no error:

if(if(vFormula='1', count({\$<Type='Red'>}, count({\$<Type='green'>})>0,Ordernumber)

The syntax according to qlik is correct. The first if statement inside should differentiate between vFormula 1 and 0. The outer If shoud say whatever inside has a count than give me the ordernumber of it. Do you have an idea why it only works after aggregate once again to the dimension "ordernumber"?

Thanks for the tip with pick/match. I will look it up.

Best.

Could be because you are using this as dimenion? If it is dimension Qlik should understand the functionlity of Aggregare of dimension on what you are preferring?