Count Nulls

[#1]

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

[Miguel_Angel_Baeyens]

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

[#3]

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 .......

[Miguel_Angel_Baeyens]

Hi,

Use the LOAD always before the SQL statement:

Table:

LOAD Field1,

   field2

;

SQL SELECT Field1, field2

FROM ...

Hope that helps,

Miguel

[#5]

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???

[Miguel_Angel_Baeyens · Accepted solution]

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