Empty cells fill up with custom text

[MindaugasBacius]

Hello,

The script looks like this:

Name_surname

Load

    Name,

    Surname

From;

Left Join (Name_surname)

    Surname,

    Birthday

From;

The result is:

Name	Surname	Birthday
John	Case	1987-01-02
Eve	Draft	-
Sue	Greg	1990-09-07
...	...	...

Issue:

How to fill up empty, '-' cells in column Birthday with custom text?

Thank you all

Mindaugas

[roger_stone]

Left Join (Name_surname)

    Surname,

    IF(ISNULL(Birthday),'Unknown', Birthday)

From;

[maxgro · Accepted solution]

Map:

Mapping load Surname, if(len(trim(Birthday))=0, 'Unknown 1', Birthday)

From .....;

Name_surname

Load

    Name,

    Surname,

    ApplyMap('Map', Surname, 'Unknow 2')

From ......;

[Anonymous]

Alternatively, you could go into your chart properties and assign whatever value you want to missing values. The default value is the dash you are seeing, but you can change it to what ever you want.  Put Missing or Unknown or To Be Determined...   You can also change the value for null in the box to the left.

[reddy-s]

Hi Bacius,

Try setting the environment variable using this:

set NullValues = "<Unknown>";

This should replace all the null values with the string specified.

[MarcoWedel]

Hi,

another solution could be:

Name_surname:
LOAD * Inline [
    Name, Surname
    John, Case
    Eve, Draft
    Sue, Greg
];

tabBirthday:
LOAD * Inline [
    Surname, Birthday
    Case, 1987-01-02
    Greg, 1990-09-07
];

LOAD Surname,
    'custom text' as Birthday
Resident Name_surname
Where not Len(Lookup('Surname','Surname',Surname,'tabBirthday'));

maxgro's ApplyMap approach should be faster though.

hope this helps

regards

Marco

[#7]

Hi,

go to presentation tab and select the field ( Birthday) and give the text instead of - in Null Symbol text box.

[MindaugasBacius]

How to use Mapping in Inline statement?

That's two tables that need to be joined via Mapping:

tabBirthday: 

LOAD * Inline [ 

    Surname, Birthday 

    Case, 1987-01-02 

    Greg, 1990-09-07 

];

Name_surname: 

LOAD * Inline [ 

    Name, Surname 

    John, Case 

    Eve, Draft 

    Sue, Greg 

]; 

[maxgro]

tabBirthday:

Mapping LOAD * Inline [

    Surname, Birthday

    Case, 1987-01-02

    Greg, 1990-09-07

];

Name_surname:

LOAD

  Name,

  Surname,

  ApplyMap('tabBirthday', Surname, 'not found birthday for ' & Surname) as Birthday  

Inline [

    Name, Surname

    John, Case

    Eve, Draft

    Sue, Greg

];