How to handle null values when loading a crosstable?

[#1]

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.

[#2]

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]

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

[#4]

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.

[#5 · Accepted solution]

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!

[#6]

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

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

Thank a lot.

Regards