topic Re: How to allocate a default value if the data is missing in QlikView

How to allocate a default value if the data is missing

Mon, 12 May 2014 15:53:59 GMT

Hi,

a bit of coding help required. I have two spreadsheets from two different sources. In one of the spreadsheets that I am loading, it does not hold data for the country (wierd I know).

what I want to do is assign a text value during the load process that will identify that the data is missing. Add in the word 'Missing'.

I have tried the IsNull but I don't think the source field is being recognised as a null, just a Blank or empty string.

How do I code around this?

All replies gratefully recieved.

Jim

Re: How to allocate a default value if the data is missing

PrashantSangle — Mon, 12 May 2014 15:57:19 GMT

Hi,

Try

if(len(Trim(fieldName))<1,'Missing',fieldName)

if(Isnull(fieldName),'Missing',FieldName)

Re: How to allocate a default value if the data is missing

alexandros17 — Mon, 12 May 2014 15:57:58 GMT

Load

...

if(isnull(Country) or Trim(Country)='', 'Missing', Country) as Country

...

Resident myTable;

Let me know

Re: How to allocate a default value if the data is missing

Mon, 12 May 2014 16:01:35 GMT

Thank you - worked a treat

Re: How to allocate a default value if the data is missing

Mon, 12 May 2014 16:02:02 GMT

Thanks Alessandro - very helpful