Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nicolai_moller
Contributor
Contributor

Flag rows

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.

CaseNameActivitystartdateenddatestatus
A101-01-201501-02-2015Done
A101-04-201501-05-2015Done
A202-02-201503-03-2015Waiting
C102-03-201502-04-2015Done
C105-01-201505-06-2015Waiting
1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

4 Replies
swuehl
MVP
MVP

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;

nicolai_moller
Contributor
Contributor
Author

It almost works...if status is empty it counts as done with your method.

swuehl
MVP
MVP

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;

nicolai_moller
Contributor
Contributor
Author

Works great. Thanks