Set analysis

[markgraham123]

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.

[swuehl]

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 ...;

[mvaugusto]

Yes you can!

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

Thanks

[markgraham123]

Thanq

[markgraham123]

Thanq