Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Help needed here...I need to create a flag that indicates if all activity 1 on a case are done.
So in the table below, CaseName A is good because all activity 1 are done.
But in CaseName C, there is one Activity 1 that is not done, so that case should not be flagged as done.
How can this be done in the load script. Thanks.
CaseName | Activity | startdate | enddate | status |
---|---|---|---|---|
A | 1 | 01-01-2015 | 01-02-2015 | Done |
A | 1 | 01-04-2015 | 01-05-2015 | Done |
A | 2 | 02-02-2015 | 03-03-2015 | Waiting |
C | 1 | 02-03-2015 | 02-04-2015 | Done |
C | 1 | 05-01-2015 | 05-06-2015 | Waiting |
You have NULLs in field status and want to consider them as 'not done'? Then try maybe (there are probably better ways (like replacing the NULLs with a value):
LEFT JOIN (Cases)
LOAD CaseName,
if(only( if(Activity = 1, if(len(trim(status)),status,'UNKNOWN')) ) = 'Done', 'Done', 'not done') as Flag
RESIDENT Cases
GROUP BY CaseName;
Maybe like this
Cases:
LOAD CaseName,
Activity,
startdate,
enddate,
status
FROM YourTable;
LEFT JOIN (Cases)
LOAD CaseName,
if(only( if(Activity = 1, status) ) = 'Done', 'Done', 'not done') as Flag
RESIDENT Cases
GROUP BY CaseName;
It almost works...if status is empty it counts as done with your method.
You have NULLs in field status and want to consider them as 'not done'? Then try maybe (there are probably better ways (like replacing the NULLs with a value):
LEFT JOIN (Cases)
LOAD CaseName,
if(only( if(Activity = 1, if(len(trim(status)),status,'UNKNOWN')) ) = 'Done', 'Done', 'not done') as Flag
RESIDENT Cases
GROUP BY CaseName;
Works great. Thanks