Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;