topic Re: Translating the word NULL in QlikView

Translating the word NULL

Wed, 03 Jun 2015 13:35:16 GMT

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.

Re: Translating the word NULL

swuehl — Wed, 03 Jun 2015 15:20:32 GMT

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,

...

Re: Translating the word NULL

buzzy996 — Wed, 03 Jun 2015 16:08:06 GMT

see tis,

Re: Translating the word NULL

Wed, 03 Jun 2015 21:06:42 GMT

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