Mapping a blank?

[richnorris]

Hi, seems like this should be a simple task, but what I'm trying to do is basically

if(isnull(emcg), '<Unknown>', if(emcg = '', '<Unknown>', emcg))) AS emcg

essentially check for nulls and also for blanks (empty quotes, ''). This would work better as a mapping load:

NullMap:

Mapping Load * Inline [ null(), <Unknown>

'', <Unknown>];

but this doesn't seem to work, because in mapping loads you dont use quotes, but it doesn't make any sense to just put a comma as the first character of a line, is there some special character that represents no character?

Many, many thanks

[#2 · Accepted solution]

Hi,

I never use 'isnull' tests as there are apparently inconsistencies in 64 bit processing. I prefer to do a length test:

if (len(emcg) = 0, '<Unknown>' , emcg) as emcg

this also has the advantage that a zero length test captures both a null value and an empty string.

I dont see how you can use a mapping as by nature null is not 'nothing' but actually 'undefined', so I would stay with the conditional test.

Regards,

Gordon

[richnorris]

Ah, interesting, didn't know there were inconsistencies, I'll bear that in mind, thanks! Doing a null map is actually a fairly common process, but I haven't seen anyone also check for empty strings with this method, which is what I need to do. I'll give the len() method a go, that sounds like a solid plan!

[sathishkumar_go]

Hi Gordon,

Thanks ya. This is gud method. I am also facing the problem for null mapping.

-Sathish

[#5]

Use Autogenerate.

BlankMap:
MAPPING LOAD
'', 'FALSE'
AUTOGENERATE 1 ;