topic Re: Nested if statement in QlikView

Nested if statement

vilstrup — Tue, 17 Apr 2018 13:42:12 GMT

Hi,

I am trying to make and if statement in a chart expression with the following issues:

sum(

If(DATAAREAID_Trans='rme',COSTAMOUNTPOSTED)

If(DATAAREAID_Trans='sdx',COSTAMOUNTPOSTED*(EXCHRATE/100) where EXCHRATE = 'USD'

If(DATAAREAID_Trans='sol',COSTAMOUNTPOSTED*(EXCHRATE/100) where EXCHRATE = 'PLN'

If(DATAAREAID_Trans='rtk',COSTAMOUNTPOSTED*(EXCHRATE/100) where EXCHRATE = 'EUR'

)

Can anybody help me with this?

Kind regards Nicolai

Re: Nested if statement

jaumecf23 — Tue, 17 Apr 2018 13:53:09 GMT

Try this:

sum(

If(DATAAREAID_Trans='rme',COSTAMOUNTPOSTED)

If(DATAAREAID_Trans='sdx',COSTAMOUNTPOSTED*(only({<EXCHRATE ={USD}>}EXCHRATE)/100)

If(DATAAREAID_Trans='sol',COSTAMOUNTPOSTED*(only({<EXCHRATE ={PLN}>}EXCHRATE)/100)

If(DATAAREAID_Trans='rtk',COSTAMOUNTPOSTED*(only({<EXCHRATE ={EUR}>}EXCHRATE)/100)

)

Re: Nested if statement

vilstrup — Tue, 17 Apr 2018 13:59:01 GMT

Hi Jaume,

it seems there is something not right ?

Re: Nested if statement

jaumecf23 — Tue, 17 Apr 2018 14:12:05 GMT

Try this:

If(DATAAREAID_Trans='rme',sum(COSTAMOUNTPOSTED),

If(DATAAREAID_Trans='sdx',sum(COSTAMOUNTPOSTED)*(only({<EXCHRATE ={USD}>}EXCHRATE )/100),

If(DATAAREAID_Trans='sol',sum(COSTAMOUNTPOSTED)*(only({<EXCHRATE ={PLN}>}EXCHRATE )/100),

If(DATAAREAID_Trans='rtk',sum(COSTAMOUNTPOSTED)*(only({<EXCHRATE ={EUR}>}EXCHRATE )/100),

))))

What I see in your original post. Is the Currency name and the currency value has the same field name : EXCHRATE . I expect that you should have two different field names for these values.

Re: Nested if statement

vishsaggi — Tue, 17 Apr 2018 14:14:04 GMT

Try this may be:

= sum(

If(DATAAREAID_Trans='rme',COSTAMOUNTPOSTED,

If(DATAAREAID_Trans='sdx',COSTAMOUNTPOSTED*(only({<EXCHRATE ={USD}>}EXCHRATE)/100),

If(DATAAREAID_Trans='sol',COSTAMOUNTPOSTED*(only({<EXCHRATE ={PLN}>}EXCHRATE)/100),

If(DATAAREAID_Trans='rtk',COSTAMOUNTPOSTED*(only({<EXCHRATE ={EUR}>}EXCHRATE)/100), 0))))

)

Sorry above expr does not work completely forgot about Only() function used. You have to use Aggr if you have two functions. Like sum() and Only

So probably you want o add some dimensions in your aggr() like

= Sum(Aggr(Sum(yourIfConditions), Dim1))

Re: Nested if statement

vilstrup — Wed, 18 Apr 2018 06:45:35 GMT

Hi Jaume,

Thank you very much for taking your time to help on this matter.

Actually i have a an Exchange table with 3 fields.

FROMDATE, EXCHRATE and CURRENCYCODE.So i just realised that it should be something more like this:

If(DATAAREAID_Trans='rme',sum(COSTAMOUNTPOSTED),

If(DATAAREAID_Trans='sdx',sum(COSTAMOUNTPOSTED)*(only({<CURRENCYCODE ={USD}>}EXCHRATE )/100),

If(DATAAREAID_Trans='sol',sum(COSTAMOUNTPOSTED)*(only({<CURRENCYCODE ={PLN}>}EXCHRATE )/100),

If(DATAAREAID_Trans='rtk',sum(COSTAMOUNTPOSTED)*(only({<CURRENCYCODE ={EUR}>}EXCHRATE )/100),

))))

It doesn't work though. it only works for the first if statement where its just COSTAMOUNTPOSTED?

- Nicolai

Re: Nested if statement

jaumecf23 — Wed, 18 Apr 2018 07:17:21 GMT

Is it possible to you to attach the qvw file? I will try to find a solution.

Re: Nested if statement

jaumecf23 — Wed, 18 Apr 2018 10:56:08 GMT

Try this new Expression:

sum({<DATAAREAID_Trans={rme}>}COSTAMOUNTPOSTED)+

sum({<DATAAREAID_Trans={sdx}>}COSTAMOUNTPOSTED)*alt(max({<CURRENCYCODE ={USD}>}EXCHRATE /100),0)+

sum({<DATAAREAID_Trans={sol}>}COSTAMOUNTPOSTED)*alt(max({<CURRENCYCODE ={PLN}>}EXCHRATE /100),0)+

sum({<DATAAREAID_Trans={rtk}>}COSTAMOUNTPOSTED)*alt(max({<CURRENCYCODE ={EUR}>}EXCHRATE /100),0)+

sum({<DATAAREAID_Trans={bsl}>}COSTAMOUNTPOSTED)*alt(max({<CURRENCYCODE ={CFH}>}EXCHRATE /100),0)

Re: Nested if statement

vilstrup — Wed, 18 Apr 2018 11:19:22 GMT

Thank you so much Jaume,

it finally works!

Regards Nicolai