topic replace null values with "missing" in Water Cooler

replace null values with "missing"

Anonymous — Thu, 06 Mar 2014 10:41:47 GMT

Hi,

I have to show "missing" instead of null in the fields with null values in the final table obtained after concatenation.

Re: replace null values with "missing"

jonathandienst — Thu, 06 Mar 2014 10:47:07 GMT

This is best done at reload time with script like this:

LOAD ....

If(IsNull(MyField), 'MISSING', MyField) As MyField,

....

(You can use If(Len(MyField) = 0, .. ) instead - it will detect nulls and empty strings).

HTH

Jonathan

Re: replace null values with "missing"

tresB — Thu, 06 Mar 2014 10:48:09 GMT

Try like:

NullAsValue FieldA;

Set NullValue = 'Missing' ;

Load FieldA,B from <>;

Re: replace null values with "missing"

amit_saini — Thu, 06 Mar 2014 10:57:04 GMT

Hi Abhinav,

Please use this script:

If(IsNull(Your_Field), 'missing', Your_Field) as Field,

Thanks,

Re: replace null values with "missing"

sunilkumarqv — Thu, 06 Mar 2014 11:23:05 GMT

Hi Abhinav,

These helps me!. hope it will apply you to.

MissingMap:

MAPPING LOAD

null(), 'MISSING!' AutoGenerate 1;

MAP FieldName USING MissingMap;

//The mapping will be applied when the field value is created. So how do we apply the map to an already existing table? //We make a copy of the table. That will cause the latest mapping to be applied

NewTable:

NoConcatenate // This is important! We want a new table!

LOAD * RESIDENT Orders;

DROP TABLE Orders; //

Re: replace null values with "missing"

Thu, 06 Mar 2014 11:28:38 GMT

Hi Abhinav,

Apart from all the options suggested above , you can do it in front end by going to Properties -- Presentation tab and giving the Null and Missing symbols as desired.

But of course doing it from the script is always the best option. 🙂

Re: replace null values with "missing"

Anonymous — Thu, 06 Mar 2014 11:30:59 GMT

Thanks a lot everyone.It worked.

Re: replace null values with "missing"

Thu, 06 Mar 2014 11:31:38 GMT

Hi Abhinav,

I would suggest you to implement in the chart.

For ex:- straight table ( see the image attached) instead of the word 'missing' you can give your own word.

Hope this helps!

Vinoth