topic Re: Flag rows in QlikView

Flag rows

nicolai_moller — Wed, 20 May 2015 07:39:41 GMT

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

Re: Flag rows

swuehl — Wed, 20 May 2015 07:46:23 GMT

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;

Re: Flag rows

nicolai_moller — Wed, 20 May 2015 08:07:11 GMT

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

Re: Flag rows

swuehl — Wed, 20 May 2015 08:41:15 GMT

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;

Re: Flag rows

nicolai_moller — Wed, 20 May 2015 08:59:24 GMT

Works great. Thanks