Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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