Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Count Nulls

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

DatePersonDateReturned
01/01/2012J. Bloggs10/02/2012
02/01/2012A.N Other
03/01/2012C. Clott
04/01/2012S. Else11/02/2012
1 Solution

Accepted Solutions
MVP
MVP

Re: Count Nulls

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

5 Replies
MVP
MVP

Re: Count Nulls

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

Not applicable

Re: Count Nulls

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

MVP
MVP

Re: Count Nulls

Hi,

Use the LOAD always before the SQL statement:

Table:

LOAD Field1,

   field2

;

SQL SELECT Field1, field2

FROM ...

Hope that helps,

Miguel

Not applicable

Re: Count Nulls

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

MVP
MVP

Re: Count Nulls

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

Community Browser