topic Re: Count Nulls in QlikView

Count Nulls

Tue, 25 Sep 2012 15:43:20 GMT

Afternoon, I've done a quick search but can't quite find the answer I need.

Basically I'm loading in data like below and beed to only show the rows where the date returned is blank, how do I write this as an expression.

I don't think I can do this via the load script as I need to calculate other things.

Can anyone help?

Thanks

Date	Person	DateReturned
01/01/2012	J. Bloggs	10/02/2012
02/01/2012	A.N Other
03/01/2012	C. Clott
04/01/2012	S. Else	11/02/2012

Re: Count Nulls

Miguel_Angel_Baeyens — Tue, 25 Sep 2012 15:46:30 GMT

Hi,

I'd do that in the load script, using the Len() function, it will perform faster and will make easier to count them in the expressions:

Table:
LOAD Person,
     Date,
     DateReturned,
     If(Len(DateReturned), 0, 1) AS NullCounter
FROM ...

The expression should be now as simple as:

Sum(NullCounter)

Or tu sum up the Sales field only for those who have nulls:

Sum({< NullCounter = {1} >} Sales)

But if you still want to do it in an expression, do something like the following:

Only({< DateReturned = {"=Len(DateReturned) = 0"} >} Person)

Hope that helps.

Miguel

Re: Count Nulls

Tue, 25 Sep 2012 16:06:07 GMT

that doesn't seem to be laoding correctly - this is what my script looks like

SQL

SELECT search_Incident_Custom_Joined.IncidentID,
search_Incident_Injuries.DateReturnedToWork,
search_Incident_Injuries.InvolvedPerson,
If(Len(DateReturnedToWork),0,1) AS NullCounter

FROM .......

Re: Count Nulls

Miguel_Angel_Baeyens — Tue, 25 Sep 2012 16:35:06 GMT

Hi,

Use the LOAD always before the SQL statement:

Table:

LOAD Field1,

field2

;

SQL SELECT Field1, field2

FROM ...

Hope that helps,

Miguel

Re: Count Nulls

Wed, 26 Sep 2012 08:30:43 GMT

sorry that is still not working, when I reload there is the following error "Incorrect syntax near the keyword 'If'."

I've added the Load statement before the SQL, everything still works except for this line

If(Len(DateReturnedToWork),0,1) AS NullCounter

Have I missed something???

Re: Count Nulls

Miguel_Angel_Baeyens — Wed, 26 Sep 2012 11:12:38 GMT

Hi Melanie,

Actually yes. The IF() function in this case is a QlikView function, not a SQL function. Your script should look like this:

// This part may change, and you may or may not not require the table name before the field name
Table:
LOAD IncidentID, 
          DateReturnedToWork,
          InvolvedPerson,
          If(Len(DateReturnedToWork), 0, 1) AS NullCounter
;
SQL SELECT search_Incident_Custom_Joined.IncidentID, 
          search_Incident_Injuries.DateReturnedToWork,
          search_Incident_Injuries.InvolvedPerson
FROM .......

Hope that makes sense now.

Miguel