Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to handle null values when loading a crosstable?

I have data in a crosstable (see attached spreadsheet) that describes when consultants were working on which projects. However, the data has null values in it (when a consultant wasn't working on a project). I want to replace these null values with zeros.

I tried to convert them in the original LOAD with an IF statement "if(isnull(<date>), 0, <date>)", where <date> is each of the date columns in the original spreadsheet. However, this won't work because I am not in control of this spreadsheet, and new date columns are being added every week and they need to be handled automatically.

Any ideas?

Thanks.

1 Solution

Accepted Solutions
Not applicable
Author

Peter, you pointed me in the right direction.

The solution has three parts to it.

  1. Turn on NullAsValue for all fields using NullAsValue *;
  2. Make null values be evaluated to zero using SET NullValue = 0;
  3. Load the data from the spreadsheet into a temp table, then do the crosstable load from the temp table into the real table.


Thanks Peter for the help!

View solution in original post

5 Replies
Not applicable
Author

Hi Simon,

what do you think about loading only the dates in a seperate statement and then do a JOIn to make sure every combination of consultant and date appears in your result table? The field value may still be NULL but the record should appear. You can then go with your if(isnull()... manipulation to replace NULLs by 0.

cheers

Florian

prieper
Master II
Master II

There might be something related to the interpreter of xlsx. If possible, convert the file to .xls and try the fllwg script:

SET NULLINTERPRET=0;
CROSSTABLE(ProjectDate, Data, 3) LOAD
*
FROM
[.\ExampleForecast.xls]
(biff, embedded labels, table is Sheet1$);


Tried also to read the file via ODBC with a preceding
SET NULLDISPLAY = 0;
but then you loose the information on the data, the columns will be read as F4, F5 etc

HTH
Peter

Not applicable
Author

Florian, thanks for the response.

It sounds promising, but I;m not sure I follow. Would you mind posting an example of some kind to help me grasp what you are saying?

Thanks.

Not applicable
Author

Peter, you pointed me in the right direction.

The solution has three parts to it.

  1. Turn on NullAsValue for all fields using NullAsValue *;
  2. Make null values be evaluated to zero using SET NullValue = 0;
  3. Load the data from the spreadsheet into a temp table, then do the crosstable load from the temp table into the real table.


Thanks Peter for the help!

View solution in original post

Not applicable
Author

Genial! Esto funcionó perfecto para mi problema!! Muchas gracias!

Great!! This answer help me to solve mi problem!

Thank a lot.

Regards