Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
markgraham123
Specialist
Specialist

Flag in If condition with 'Not Exists' functionality

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;


View solution in original post

7 Replies
sunny_talwar

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;

swuehl
MVP
MVP

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;


sunny_talwar

Elegant

maxgro
MVP
MVP

like this?


1.png

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

markgraham123
Specialist
Specialist
Author

Thanks bro. Sunny

markgraham123
Specialist
Specialist
Author

Thanks swueh1

markgraham123
Specialist
Specialist
Author

Thanks maxgro