Amount field - based on condition

[qlikviewaf]

Hi Guys,

i have a table with following values:

• Company
• Currency
• Amount X
• Amount Y

Basically i would like to create another field based on the following condition:

If Company= 00010 and Currency = USD -> "Amount field"(new one) should be AMOUNT X

If Company= 00010 and Currency <> USD -> "Amount field"(new one) should be AMOUNT Y

If Company= 00030 and Currency = EUR -> "Amount field"(new one) should be AMOUNT Y

If Company= 00030 and Currency <> EUR -> "Amount field"(new one) should be AMOUNT X

how i can build this new calculated field?

Thank you,

[kamal_sanguri · Accepted solution]

In Script :

If (Company= '00010' and Currency = 'USD',[AMOUNT X],

     If(Company= '00010' and Currency <> 'USD', [AMOUNT Y],

          If(Company= '00030' and Currency = 'EUR', [AMOUNT Y],

               If(Company= '00030' and Currency <> 'EUR',[AMOUNT X])))) as [Amount field]

[passionate]

Hi Antonio,

I would suggest create a flag in the table based on condition and then use the flag value in charts or calculation.

Requirement:

Company	Currency	Flag	X	Y
00010	USD	X	2100	1200
00010	EUR	Y	200	2150
00030	USD	X	2300	1200
00030	EUR	Y	1500	4586

Straight Table:

Dimension1: Company

Dimension2: Currency

Expression:

Pick(Match(Flag,'X','Y'),Sum(X),Sum(Y))

Chart will look like:

Company	Currency	Pick(Match(Flag,'X','Y'),Sum(X),Sum(Y))
10	EUR	2150
10	USD	2100
30	EUR	4586
30	USD	2300

Regards,

Pankaj

[qlikviewaf]

I guess that for performance point of view is better.

How can i create the flag?

[passionate]

Hi Antonio,

Flag can be created by similar approach which kamal used in script:

If (Company= '00010' and Currency = 'USD',[AMOUNT X],

     If(Company= '00010' and Currency <> 'USD', [AMOUNT Y],

          If(Company= '00030' and Currency = 'EUR', [AMOUNT Y],

               If(Company= '00030' and Currency <> 'EUR',[AMOUNT X])))) as [Amount field]

I would suggest use Pick(Match()) instead of multiple If's For better performance

Creating flag will help if this scenario repeats on your dashboard.

Regards,

Pankaj

[kamal_sanguri]

Hi Pankaj,

I don't get the point of creating Flag first in Script and then using an expression in UI to get the value calculated. When everything can be done at the script level using the same script to create Flag and nothing is required at UI.

Am I missing here something???