topic Create Flag colun in QlikView

Create Flag colun

yashcena — Tue, 20 Mar 2018 11:02:33 GMT

I have a dataset which looks like this:

Each account id has multiple products.

I have to create a table which should have a column named XYZ_FLAG (y/n).

If an account id has either of aaa,bbb,eee products and doesnt have either xxx, www products, then this flag column should be marked Y.

The table would like this:

1011 has aaa, bbb and eee products and does not have xxx or www products, Y

1012 has aaa product and does not have xxx or www, Y

1013 had bbb and does not have xxx or www, Y

1014 has eee but it also has xxx, N

1015 does not have either aaa or bbb or eee, N

Similarly there should be another column ABC_FLAG (y/n)

If an account id has either of ddd,eee,xxx products and doesnt have hhh product, then this flag column should be marked Y.

1011 has ddd and eee and does not have hhh, Y

1012 does not have ddd or eee or xxx, N

1013 does not have ddd or eee or xxx, N

1014 has ddd and eee and does not have hhh, Y

1015 does not have ddd or eee or xxx, N

Is this doable in qlikview?

I hope I have made my requirement clear.

Thanks,

YASH

Re: Create Flag colun

PrashantSangle — Tue, 20 Mar 2018 11:10:13 GMT

Hi,

Try below

Load Acct_id,

if(not wildmatch(NewProduct ,'xxx','www'),'N','Y') as xyz_flag,

if(not wildmatch(NewProduct ,'hhh'),'N','Y') as xyz_flag;

Load Acct_id,concat(Product,',') as NewProduct from table;

Regards

Re: Create Flag colun

olivierrobin — Tue, 20 Mar 2018 11:17:16 GMT

hello

may be this

first load your data in a table (here named p)

and then create a second table

load ID,
if(max(if(match(Product,'aaa','bbb','eee'),1,0))*max(if(match(Product,'xxx','www'),0,1))=1,'Y','N') as XYZ_FLAG,
if(max(if(match(Product,'ddd','eee','xxx'),1,0))*max(if(match(Product,'hhh','hhh'),0,1))=1,'Y','N') as ABC_FLAG
resident p
group by ID