Create a flag in script with group by

[markgraham123]

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.

[sunny_talwar]

Not sure why you need a MaxString here Mark?

[swuehl · Accepted solution]

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

[markgraham123]

Sunny,.

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

[markgraham123]

Swuehl,

U just answered the question directly.

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

It worked.

Thank you

[sunny_talwar]

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.

[rwunderlich]

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