topic Re: If Then Else error in QlikView

If Then Else error

gfisch13 — Sat, 16 Nov 2024 01:55:02 GMT

Hi - after completing this conditional statement and using it in one of my charts I realized I made an error and do not get my desired result..

Statement: IF([CC_MCC_Desc] = 'Grocery Stores and Supermarkets', Sum([CC_ITEM_COST]), Sum([CC_Tran_Amt]))

this statement works in the chart only when the value 'Grocery Stores and Supermarkets' is selected In my CC_MCC_Desc list box.

what I really need to do is have it function all the time aeven when this specific selection isn't made since there is a mixture of records. What did I do wrong?

Re: If Then Else error

simrankaur — Tue, 19 Nov 2019 07:48:14 GMT

Try this:

IF([CC_MCC_Desc] = 'Grocery Stores and Supermarkets', Sum({<[CC_MCC_Desc] = {'Grocery Stores and Supermarkets'}>}[CC_ITEM_COST]), Sum([CC_Tran_Amt]))

Re: If Then Else error

jaibau1993 — Tue, 19 Nov 2019 07:53:00 GMT

Hi!

In your expression, when you write the condition [CC_MCC_Desc] = 'Grocery Stores and Supermarkets', it is equivalent to Only([CC_MCC_Desc]) = 'Grocery Stores and Supermarkets'. This is because in an expression you must aggregate data using agregation functions (like sum, count, max, only...). Thus, if you do not use agregation functions Qlik uses Only as default.

Said so, what is the logic of your expression? I mean, if nothing is selected on CC_MCC_Desc, how should it behave?

Regards,

Jaime.

Re: If Then Else error

tresB — Tue, 19 Nov 2019 08:57:00 GMT

May be this:

IF(Max(Match([CC_MCC_Desc], 'Grocery Stores and Supermarkets'))=1, Sum([CC_ITEM_COST]), Sum([CC_Tran_Amt]))

This expression would work (with/without selection) in checking of that specific value in the field.

Re: If Then Else error

jaibau1993 — Tue, 19 Nov 2019 09:11:31 GMT

Hi Tresesco!

Will it work? I don't get the logic. Is Match evaluated for each CC_MCC_Desc record without the need of Aggr function?

As equivalent to what you purpose, I would have write something like

If( Index(Concat(CC_MCC_Desc), 'Grocery Stores and Supermarkets'), then, else)

Regards,

Jaime.

Re: If Then Else error

tresB — Tue, 19 Nov 2019 09:54:48 GMT

Hi Jaime,

Aggr function is there (max) in my expression; you are using index outside it, I am using using it inside. Both will work. Give a try in a sample data to find a specific string/value. 🙂

Re: If Then Else error

jaibau1993 — Tue, 19 Nov 2019 10:08:28 GMT

Hi Tresesco:

What I did not know (and it is what confused me) is that Match returns a list of values when used as you did:

Nice lesson learned today!

Regards,

Jaime.

Re: If Then Else error

tresB — Tue, 19 Nov 2019 10:28:30 GMT

This is nothing special about match() - I would say. It's rather because of aggregation function you use outside. The similar behavior you would see with other functions as well. Here index() would be similar utility case. I.e. - you can use index() in exact similar fashion.

Re: If Then Else error

gfisch13 — Thu, 21 Nov 2019 19:34:12 GMT

Thank you all for the learning opportunity!