topic Re: nested if at back end script? in QlikView

nested if at back end script?

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

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

tresB — Wed, 05 Jul 2017 05:45:00 GMT

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.

tresB — Wed, 05 Jul 2017 05:57:54 GMT

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;