topic Re: Set Null if Len(Field) = 0 in QlikView

Set Null if Len(Field) = 0

Thu, 05 Mar 2015 13:20:44 GMT

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

Re: Set Null if Len(Field) = 0

giakoum — Thu, 05 Mar 2015 13:23:28 GMT

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

Re: Set Null if Len(Field) = 0

giakoum — Thu, 05 Mar 2015 13:24:29 GMT

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

Re: Set Null if Len(Field) = 0

Anonymous — Thu, 05 Mar 2015 13:25:22 GMT

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

Re: Set Null if Len(Field) = 0

Roop — Thu, 05 Mar 2015 13:36:30 GMT

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:

Re: Set Null if Len(Field) = 0

Thu, 05 Mar 2015 13:37:36 GMT

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?

Re: Set Null if Len(Field) = 0

Thu, 05 Mar 2015 13:54:50 GMT

I'm agree with you.

But how can I standardize differents values?

Re: Set Null if Len(Field) = 0

chrislofthouse — Thu, 05 Mar 2015 14:07:47 GMT

Beaulieu Mathieu wrote:

I'm agree with you.

But how can I standardize differents values?

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

Re: Set Null if Len(Field) = 0

Roop — Thu, 05 Mar 2015 14:09:52 GMT

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

Re: Set Null if Len(Field) = 0

Thu, 05 Mar 2015 15:50:41 GMT

you can use if condition as well

Re: Set Null if Len(Field) = 0

rwunderlich — Thu, 05 Mar 2015 16:21:12 GMT

See this post

http://qlikviewcookbook.com/2013/01/filling-default-values-using-mapping/

for a suggested method of standardizing and converting your blanks to null(). Similar to Rupert's ApplyMap() suggestion but MAP USING can be easier to use when you have many fields.

-Rob

Re: Set Null if Len(Field) = 0

vishsaggi — Thu, 05 Mar 2015 16:47:13 GMT

As a Novice QV developer I usually handle nulls using the following

- First convert the missing values to Null
- Then replacing the Null Values to Desired Values.

- In the edit script 'Main tab' Set the variables as below

SET NullIntrepret = ''; // If loading data from Table files or Inline tables
SET NullDisplay =''; // If Loading data using ODBC/OLEDB connections

Then using ISNULL or ALT functions to transform accordingly.

Eg Data:

Script:

Table1:
LOAD Dates,
Article,
if(IsNull(Article),'No Data',Article) AS [ifNullHandled],
Alt(Article,'NoValue') AS [AltNullHandling]
FROM

(ooxml, embedded labels, table is Sheet1);

Any insights on the above will be much appreciated.