topic Re: Is Null in a set analysis dimension in QlikView

Is Null in a set analysis dimension

Fri, 16 Mar 2012 14:45:40 GMT

Hi everyone,

I am struggling to find a way to show where a record is null in set analysis, I have lokoed around the site and not had much muck with getting something to work and was wondering if any one might have any suggestions?

I have a table with Incidentnumber and %KEY_CARS, where the key cars is a string derived from the incident number into a date and number.

Another table is for paperwork and also has the %KEY_CARS id and an additional field %KEY_PRFUID which can only be populated if there is a form.

there will always be a %key_cars value for every incident but not always a %key_prfuid value for each key cars.

this is what I have so far:

=count({<%KEY_PRFUID= -{},OTBOnlyFlag= -{1},time_first_clinical_unit_on_scene= -{}, ActualIncidents= {1}>}distinct IncidentNumber)

Should I be doing something else to the %KEY_PRFUID= -{} part?

Thanks for any guidance.

Is Null in a set analysis dimension

jvitantonio — Fri, 16 Mar 2012 15:53:10 GMT

Add ' ' if the field is null blank

=count({<%KEY_PRFUID= -{''},OTBOnlyFlag= -{1},time_first_clinical_unit_on_scene= -{''}, ActualIncidents= {1}>}distinct IncidentNumber)

Re: Is Null in a set analysis dimension

Mon, 19 Mar 2012 09:55:09 GMT

Hi, thanks for the reply.

I treid this and it returns all fields where there is something in the PRFUID field. I tested this by changing

KEY_PRFUID= -{''},

KEY_PRFUID= {'*'},

and I get the same result. Any other ideas, could I do something to see if the field is not a NUM perhaps?

Re: Is Null in a set analysis dimension

CELAMBARASAN — Mon, 19 Mar 2012 09:58:07 GMT

Hi,

Check by using this

KEY_PRFUID-={'*'}

=count({<%KEY_PRFUID-={'*'},OTBOnlyFlag-={1},time_first_clinical_unit_on_scene-={'*'}, ActualIncidents= {1}>}distinct IncidentNumber)

Celambarasan

Re: Is Null in a set analysis dimension

Mon, 19 Mar 2012 10:06:17 GMT

Hi Celambarasan, I have tried your suggestion and it returns zero. I'll try and post up an example.

Re: Is Null in a set analysis dimension

Mon, 19 Mar 2012 13:51:05 GMT

Hi,

I put an If statement on the dimesion, its not fast but it does do the job.

=IF(ISNULL(%KEY_PRFUID), IncidentNumber,NULL())

and then suppress if null. There must be a faster way to do this but for now I am happy (ish!).

Thanks for taking the time to give it a go.

Is Null in a set analysis dimension

Mon, 19 Mar 2012 15:23:53 GMT

Another way to do it would be to try the following set expression..

=count({<%KEY_PRFUID= {"Len(%KEY_PRFUID)>0"},OTBOnlyFlag= -{1},time_first_clinical_unit_on_scene= {"Len(time_first_clinical_unit_on_Scene)>0"}, ActualIncidents= {1}>}distinct IncidentNumber)

as it is a set it may work faster but without knowing anything about your data I can no say..

it may help in the future though

Re: Is Null in a set analysis dimension

Mon, 19 Mar 2012 16:21:11 GMT

hi again, I have uploaded a file that might help find an answer.

Re: Is Null in a set analysis dimension

Mon, 19 Mar 2012 16:56:14 GMT

Dear Phil_King,

try to use this expression: =if(isnull(%KEY_PRFUID),count(distinct IncidentNumber)).

Regards

David

Re: Is Null in a set analysis dimension

Thu, 17 May 2012 00:17:43 GMT

Hi Phil,

I was having a similar problem, but I was trying to INCLUDE records with nulls in a related value. You have to think of Set Analysis in
terms of making selections, because that’s basically what it is. You’re telling
qlikview “give me <some value> as though the following selections were
made”. In Qlikview, we know that we cannot select a null dimension, or field
value. Consequently, telling it to select something that is null via set
analysis is a contradiction. That said, there is a method that we can use to do
this. In the same way that you might select the null segment by selecting all
possible values, right clicking in a table object and selecting excluded
records, we need to do the same thing in the set to accomplish this.

The syntax is… fun. Here’s my set:

(sum({<[Referral Action
Counter] = {1}>

*<[Referees.Member ID] = E({<[Referees.First
Purchase ID] ={">0"}>} [Referees.Member ID])>

* <[Referees.Registration Date] = {">=$(vDashboardStart)"}>

* <[Referees.Registration Date] = {"<=$(vDashboardEnd)"}>

}[Referees.Member Counter]))

The part we’re interested in is this bit:

<[Referees.Member ID]
= E({<[Referees.First
Purchase ID] ={">0"}>}[Referees.Member
ID])>

Roughly translated, it says give me all of the
records (I’ve used the ID field to identify a record) that are EXCLUDED (that’s
the E) by the set defined as all referees.First Purchase IDs have a value (ie:
a value greater than 0)

Of course, I’m still in the process of QAing this,
but it SEEMS to be working… I need to throw a few more cases at it to be sure.

I’m not exactly sure what you’re trying to accomplish, but
this might set you on the right track.