Translating the word NULL

[#1]

I've been tasked to import our Complaints into QlikView from Excel so that they can be made more visual.

The author of the spreadsheet has filled any empty cells with the word 'NULL'.  When creating graphs I am doing a count on the 'Complaint process number' by 'Reason' for example and there are 19 records where the reason has been set to NULL.  I'd like to be able to replace this with the word 'Unknown' instead. How do I do this?

Apologies if this seems really straightforward... I had some training on QlikView last year and this is the first time I've been able to 'dabble' with it.

Thanks in advance.

[swuehl · Accepted solution]

You can map the reason values to a different naming, like

ReasonMap:

MAPPING LOAD In, Out [

In, Out

NULL, Unknown

];

LOAD

     ApplyMap('ReasonMap',Reason) as Reason,

     ComplainID,

     ...

FROM ExcelFile.xls ....;

A simple if() statement in the load for a single mapping would also work

LOAD

     if(Reason = 'NULL', 'Unknown', Reason) as Reason,

     ComplainID,

     ...

[buzzy996]

see tis,

[#4]

Thanks so much for this... it's worked a treat. I was almost there before I posted the question but must have been missing something