Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Not sure why you need a MaxString here Mark?
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
Sunny,.
Because i want to group it by Order No. as per my original data.
Swuehl,
U just answered the question directly.
The function i need to use is Index(Only) for the req.
It worked.
Thank you
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.
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