topic Flag in If condition with 'Not Exists' functionality in QlikView

Flag in If condition with 'Not Exists' functionality

markgraham123 — Tue, 01 Mar 2016 21:04:01 GMT

Hi All,

I was trying to create a flag which distinguishes special and normal orders.

Order, Product, INS

12345 ABC Y

12345 DEF Y

12345 GHI

12345 JKL

67891 TYU

If an order has atleast one INS specified then it by default becomes special type and give 'Y' .

Only if the order has no INS, then give 'N'.

O/P:

Flag Y has only 12345

Flag N has only 67891

Any help is highly appreciated.

Re: Flag in If condition with 'Not Exists' functionality

sunny_talwar — Tue, 01 Mar 2016 21:12:50 GMT

May be this:

Table:

LOAD Order,

Product,

If(Len(Trim(INS)) > 0, INS) as INS;

LOAD * Inline [

Order, Product, INS

12345, ABC, Y

12345, DEF, Y

12345, GHI,

12345, JKL,

67891, TYU,

];

Left Join(Table)

LOAD DISTINCT Order,

'Y' as INS1

Resident Table

Where INS = 'Y';

FinalTable:

NoConcatenate

LOAD Order,

If(Len(Trim(INS1)) = 0, 'N', INS1) as INS,

Product

Resident Table;

DROP Table Table;

Re: Flag in If condition with 'Not Exists' functionality

swuehl — Tue, 01 Mar 2016 21:13:59 GMT

Another approach:

Table1:
LOAD * Inline
[
Order, Product, INS
12345,ABC,Y
12345,DEF,Y
12345,GHI
12345,JKL
67891,TYU
];

Table2:
LEFT JOIN
LOAD Order, If(Maxstring(INS) = 'Y', 'Y', 'N') as Flag
Resident Table1
GROUP BY Order;

Re: Flag in If condition with 'Not Exists' functionality

sunny_talwar — Tue, 01 Mar 2016 21:15:07 GMT

Elegant

Re: Flag in If condition with 'Not Exists' functionality

maxgro — Tue, 01 Mar 2016 21:19:34 GMT

like this?

Table:

LOAD * Inline [

Order, Product, INS

12345, ABC, Y

12345, DEF, Y

12345, GHI,

12345, JKL,

67891, TYU,

];

Left Join(Table)

load Order, if(match(MaxString(INS), 'Y'), 'Y', 'N') as Flag

Resident Table

group by Order;

EDIT: ops, swuehl already answered

Re: Flag in If condition with 'Not Exists' functionality

markgraham123 — Tue, 01 Mar 2016 21:50:45 GMT

Thanks bro. Sunny

Re: Flag in If condition with 'Not Exists' functionality

markgraham123 — Tue, 01 Mar 2016 21:51:14 GMT

Thanks swueh1

Re: Flag in If condition with 'Not Exists' functionality

markgraham123 — Tue, 01 Mar 2016 21:51:40 GMT

Thanks maxgro