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

Set Null if Len(Field) = 0

Hi,

I don't understand why, but in my table after loading, i can see values with " - " symbole and values empty...

Is it possible to set all values with " - " ?

Exemple :

Exmpl.jpg

Thanx

11 Replies
giakoum
Partner - Master II
Partner - Master II

can it be that empty are actually empty? or contain a space? if you export t to a text file, what do you get? A sample app would help

giakoum
Partner - Master II
Partner - Master II

there are settings in presentation tab that determine null and empty representations

Capture.PNG

Anonymous
Not applicable
Author

Blanks by default mean that it is empty srting but not null.  If this is the case, you can use more or less exactly what is in your question:
if(len(trim(Article))=0, null(),Article)

Trim() helps to eliminate spaces if there are any

Roop
Specialist
Specialist

This can also happen if you have a mismatched join.

if you link these 2 tables together:

Product

ProductDate
a01-Feb
b01-Mar
c01-Apr
d01-May
e01-Jun

Product Description

ProductProduct Desc
aProduct A
bProduct B
cProduct C
dProduct D

You get the following:

example.png

Not applicable
Author

Thank you,

But i have about 40 fields... I don't want to set the condition on each field...

Is there another solution? With a loop?

Not applicable
Author

I'm agree with you.

But how can I standardize differents values?

chrislofthouse
Partner Ambassador
Partner Ambassador

Beaulieu Mathieu wrote:

I'm agree with you.

But how can I standardize differents values?

To standardise values (descriptions etc) you could use applymap.

Roop
Specialist
Specialist

I will quite often use ApplyMap to check values against the underlying tables to ensure that joins are correct. In the ApplyMap you define the field to lookup and the value of the field that does not match.

This is often set to "Unknown" and makes the system self-checking as you can then verify the joins AND view the effect of any missing records as a percentage. Depending on the system, missing data is acceptable at certain levels but always needs to be checked.

CheckProdValueMap:

Mapping Load

     Product,

     [Product Desc]

FROM

(ooxml, embedded labels, table is Sheet2);

Product:

LOAD Product,

     Date,

     ApplyMap('CheckProdValueMap',Product,'Unknown Product') as [Product Desc]

FROM

(ooxml, embedded labels, table is Sheet1);

Creates:

exampleNew.png

Hope this helps

Not applicable
Author

you can use if condition as well