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

Create a flag in script with group by

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Create a flag in script with group by

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

6 Replies

Re: Create a flag in script with group by

Not sure why you need a MaxString here Mark?

MVP
MVP

Re: Create a flag in script with group by

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

Re: Create a flag in script with group by

Sunny,.

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

markgraham123
Contributor II

Re: Create a flag in script with group by

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

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

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

Community Browser