Using where syntax and select highest value per project

[JohannesBoehmer]

Hello, 

I´ve a data model where I have a project ID and every project ID can have either one or more milstones reached. 

For example:
P10999|PM040,PM070

P50002|PM070

P60232|PM040

Now I did the following:  if(MILESTONE='PM040','1',if(MILESTONE='PM070','2','0')) as Milstone_Number. Now every PM040 has a 1 and every PM070 a 2. 

Can someone help with the syntax in the where clause? I want to choose the highest value every Project ID has now. 

Thank you!

[MayilVahanan · Accepted solution]

Hi

Try like below, it will give only the max milestone info per project.

MTemp:
Load PID, milestones, if(milestones = 'PM040', 1, if(milestones = 'PM070', 2, 3)) as Milstone_Number;
LOAD *, SubField(milstones, ',') as milestones INLINE [
PID | milstones
P10999|PM040,PM070

P50002|PM070

P60232|PM040
](delimiter is '|');

Inner Join
Load PID, max(Milstone_Number) as Milstone_Number Resident MTemp Group by PID;