nested if at back end script?

[devarasu07]

Hi All,

below script is not working, can you advise me is there any simplest way to write below logic. thanks

RepeatStatus:

Load Barcode,

if(

if(Type_ID='NB',sum(OrderQty)*.2) < if(Type_ID='RO',sum(OrderQty)),1,0)  as Repeat_Status_Flag

Resident Fact where Match(Type,'PO')

Group by Barcode;

Thanks,
Deva

[tresesco]

Try like:

RepeatStatus:

Load

          *,

          If(NBSum<ROSum, 1,0) as Repeat_Status_Flag

;

Load  Barcode,

          Sum(if(Type_ID='NB', OrderQty))*.2 as NBSum,

          Sum(if(Type_ID='RO', OrderQty)) as ROSum

Resident Fact where Match(Type,'PO') Group by Barcode;

Update: However, this seems not to be the right approach to compare. Better you should use set analysis at the front-end.

[tresesco · Accepted solution]

The solution above would not get you the desired result. You probably need to join tables like:

Temp:

Load  Barcode,

          Sum(if(Type_ID='NB', OrderQty))*.2 as NBSum

Resident Fact where Match(Type,'PO') Group by Barcode;

Join

Load  Barcode,

           Sum(if(Type_ID='RO', OrderQty)) as ROSum

Resident Fact where Match(Type,'PO') Group by Barcode;

RepeatStatus:

Load

          *,

          If(NBSum<ROSum, 1,0) as Repeat_Status_Flag

Resident Temp;

Drop Table Temp;