Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Empty cells fill up with custom text

Hello,

The script looks like this:

Name_surname

Load

    Name,

    Surname

From;

Left Join (Name_surname)

    Surname,

    Birthday

From;

The result is:

NameSurnameBirthday
JohnCase1987-01-02
EveDraft-
SueGreg1990-09-07
.........

Issue:

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

Thank you all

Mindaugas

1 Solution

Accepted Solutions
maxgro
MVP
MVP

Map:

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

From .....;

Name_surname

Load

    Name,

    Surname,

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

From ......;

View solution in original post

8 Replies
roger_stone
Creator III
Creator III

Left Join (Name_surname)

    Surname,

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

From;

maxgro
MVP
MVP

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
Not applicable

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.

chart property.PNG

reddy-s
Master II
Master II

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:

QlikCommunity_Thread_194545_Pic1.JPG

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

Not applicable

Hi,

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

MindaugasBacius
Partner - Specialist III
Partner - Specialist III
Author

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
MVP
MVP

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

];