Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Applicable88
Creator III
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
Anil_Babu_Samineni

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)

View solution in original post

4 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
Applicable88
Creator III
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. 

 

 

 

Anil_Babu_Samineni

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
Applicable88
Creator III
Creator III
Author

@Anil_Babu_Samineni thank you! you are right. the double if construct works, but only as measure not as dimension. But on the first look it returns like if its a dimension. 

Thank you very much.