topic Re: Set analysis help in QlikView

Set analysis help

Wed, 22 Feb 2012 08:14:50 GMT

Hi all,

I currently have a chart with one dimension of "consultant" and two expressions "Average knife to skin time" and "Activity".

However, this is showing dubious results as it includes activities where there was no procedure.

How do I exclude "procedure" where it is null

Thanks very much

Ian

File now attached.

Thanks

Set analysis help

jagan — Wed, 22 Feb 2012 08:28:44 GMT

Hi,

Can you attach the sample. "Average knife to skin time", "Activity" and "procedure" are fields or any values in the fields.

Regards,

Jagan.

Re: Set analysis help

Wed, 22 Feb 2012 09:15:52 GMT

I have attached the file now Jangan

Thanks

Set analysis help

jagan — Wed, 22 Feb 2012 09:56:43 GMT

Hi,

There is no data in the attached file. What data is there in Procedure field, it has numeric or text.

Regards,

Jagan.

Re: Set analysis help

Wed, 22 Feb 2012 10:12:21 GMT

Apologies, I’m new to the Qlikview game.

New file attached with data this time.

Ian

Re: Set analysis help

jagan — Wed, 22 Feb 2012 10:20:03 GMT

Hi,

Please check the attachment.

Hope it helps you.

Regards,

Jagan.

Set analysis help

ToniKautto — Wed, 22 Feb 2012 10:22:28 GMT

Working with NULL in set expression wont be that easy, since you can not make selections on NULL values in QlikView. Compare with adding a field containing NULL values in a list box, it is not possible to select NULL in such a list box.

The easiest way to include/exclude NULL in your caluclation is likely to use an IF statement and the IsNull() function. It is a bit unclear in which way you want to exclude the NULL values, so there are two approached to this as far as I can tell.

First, is if you want to exclude the Dimension related to NULL procedures. This would automaticaly also exclude the NULL values from the expressions. If so, add the Dimension as a calculated dimension instead for example like below;

=if(not isnull(Procedure), Consultant)

Second, you want to exclude the NULL values from your expression aggregations. It could look something like;

=Count(if(not isnull(Procedure), [Episode ID]))

A more sofisticated solution would be to change your script, to add a flag the NULL values. There after you could use this flag in set expressions.

Script addition could be like;

Episodes:

Load

if(isnull(Procedure), 1, 0) as ProcedureIsNullFlag

;

LOAD
autonumber(replace("episode_id",':','-'), 'EP') as %Episode_ID,
replace("episode_id",':','-') as [Episode ID],
/.../

The set expression could then look like;

=Count({$<ProcedureIsNullFlag={0}>} [Episode ID])

Re: Set analysis help

IAMDV — Wed, 22 Feb 2012 10:40:43 GMT

Hi Ian,

Please find attached example. I’m assuming that you arereferring Unknown as Null in your case. Please let me know if this is not thecase.

Here is the expression :

Sum ([Knifeto skin time]) / TextCount ({<Procedure -= {'Unknown'}>} EpisodeID)

I hope this helps!

Cheers - DV