topic Re: Create a flag in script with group by in QlikView

Create a flag in script with group by

markgraham123 — Fri, 04 Mar 2016 22:36:04 GMT

Hi All,

I'm working on a requirement where i need to create a flag grouping by Order with respect to the value in it.

LOAD [Order No],

if(match(MaxString(Inst), 'PT') OR Index(Indicator,'PP'), 'Y', 'N') as Flag

Resident Table1

Group By [Order No];

The problem is, 'PP' is not the MaxString(Value) for the filed and hence it is not fetching me the req. value.

I'm using only Max(String) as i have to always group by Order No.

Can someone pls help here.

Any function similar to MaxString() works.

Re: Create a flag in script with group by

sunny_talwar — Fri, 04 Mar 2016 22:41:20 GMT

Not sure why you need a MaxString here Mark?

Re: Create a flag in script with group by

swuehl — Fri, 04 Mar 2016 22:43:33 GMT

Sorry, I don't really get what you want to achieve.

Why do you group by order no, since there seems to be only 1 record per order no?

And why is MaxString(Value) not working for Indicator field?

If you use a group by clause, you would need aggregation functions for all fields not listed in the clause.

You can use Only() as aggregation if that's appropriate:

if(match(MaxString(Inst), 'PT') OR Index(Only(Indicator),'PP'), 'Y', 'N') as Flag

Re: Create a flag in script with group by

markgraham123 — Fri, 04 Mar 2016 22:44:35 GMT

Sunny,.

Because i want to group it by Order No. as per my original data.

Re: Create a flag in script with group by

markgraham123 — Fri, 04 Mar 2016 22:47:17 GMT

Swuehl,

U just answered the question directly.

The function i need to use is Index(Only) for the req.

It worked.

Thank you

Re: Create a flag in script with group by

sunny_talwar — Fri, 04 Mar 2016 22:47:31 GMT

There is only one Order No. only in the attached sample. Do you have duplicate in your real database? Would you be share a sample which is more representative of the actual data. Also share the expected result if possible.

Re: Create a flag in script with group by

rwunderlich — Fri, 04 Mar 2016 23:03:49 GMT

It looks like you have more than one row per order. I think concat() would be any easier approach:

LOAD [Order No],

if(wildmatch(concat(Inst,','), '*PT*')

OR wildmatch(concat(Indicator,','), '*PP*') , 'Y', 'N') as Flag

Resident Table1

Group By [Order No];

-Rob

http://masterssummit.com

http://qlikviewcookbook.com