topic Re: Set analysis in QlikView

Set analysis

markgraham123 — Fri, 18 Sep 2015 15:04:57 GMT

Hi all,

I was eager to know whether can we write a set analysis statement for the below If statement:

Aggr(If(Len(Trim(Amount))>0, Amount, Profit), ID).

I have 3 columns, ID, Amount and profit.

But for few IDs the "Amount" field is empty and hence i wanna fetch the profit field whenever i encounter a null in "Amount".

Can someone pls help here.

Re: Set analysis

swuehl — Fri, 18 Sep 2015 15:09:59 GMT

Since you want to return either Amount or Profit field value, I believe there is no way around a conditional function. But I would consider doing this stuff in the load script, not in the front end, by creating a new field:

LOAD

ID,

Profit,

Amount,

if(Len(Trim(Amount))>0, Amount, Profit) as Value,

...

FROM ...;

Re: Set analysis

mvaugusto — Fri, 18 Sep 2015 19:21:51 GMT

Yes you can!

Aggr(If(Len(Trim(Amount))>0, Amount, Profit), ID)

Thanks

Re: Set analysis

markgraham123 — Fri, 18 Sep 2015 21:43:57 GMT

Thanq

Re: Set analysis

markgraham123 — Fri, 18 Sep 2015 21:44:18 GMT

Thanq