Set Null if Len(Field) = 0

[#1]

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 :

Thanx

[giakoum]

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]

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

[Anonymous]

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]

This can also happen if you have a mismatched join.

if you link these 2 tables together:

Product

Product	Date
a	01-Feb
b	01-Mar
c	01-Apr
d	01-May
e	01-Jun

Product Description

Product	Product Desc
a	Product A
b	Product B
c	Product C
d	Product D

You get the following:

[#6]

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?

[#7]

I'm agree with you.

But how can I standardize differents values?

[chrislofthouse]

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]

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:

Hope this helps

[#10]

you can use if condition as well