Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
markgraham123
Contributor II

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.

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

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

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;


7 Replies

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

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;

MVP
MVP

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

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

Elegant

MVP
MVP

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

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
Contributor II

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

Thanks bro. Sunny

markgraham123
Contributor II

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

Thanks swueh1

markgraham123
Contributor II

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

Thanks maxgro

Community Browser