topic Re: Amount field - based on condition in QlikView

Amount field - based on condition

qlikviewaf — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Amount field - based on condition

kamal_sanguri — Thu, 15 Jun 2017 08:12:51 GMT

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]

Re: Amount field - based on condition

passionate — Thu, 15 Jun 2017 08:30:47 GMT

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

Re: Amount field - based on condition

qlikviewaf — Thu, 15 Jun 2017 09:19:33 GMT

I guess that for performance point of view is better.

How can i create the flag?

Re: Amount field - based on condition

passionate — Fri, 16 Jun 2017 06:30:22 GMT

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

Re: Amount field - based on condition

kamal_sanguri — Fri, 16 Jun 2017 06:55:53 GMT

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???