QlikView App Dev

[markgraham123]

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.

[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 · Accepted solution]

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]

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

[markgraham123]

Thanks bro. Sunny

[markgraham123]

Thanks swueh1

[markgraham123]

Thanks maxgro