topic Re: How do you assign values to nulls ? in QlikView

How do you assign values to nulls ?

Wed, 11 Dec 2013 10:05:41 GMT

Hi,

I have data for many thousands of leads drawn into QV from Salesforce. For some of the leads, certain information is missing and the fields in salesforce have null values (literally, no value. a blank cell). For example, for the leads' marketing sources we have PPC, Media, WOM, Tradeshow, etc. and there are also many leads with just no value whatsoever.

When I create a chart in QV (for example a stacked bar chart) to show the number of leads generated per month from the different marketing source (the dimension are a time dimension and the marketing source, where the month of lead generation is the horizontal axis), the null leads do show in the stacked bar, but do not show in the legend whatsoever.

I tried to map the marketing source and assign "Unknown" to nulls (just created an excel file with the name of the marketing source on the left column and the name I want to see in QV on the column next to it), but that doesn't work. So, I'm turning to you guys. Any idea how to make it happen and have the null values appear in the legend as "Unknown" ?

Thanks!

Re: How do you assign values to nulls ?

salto — Wed, 11 Dec 2013 10:09:36 GMT

Hello,

as far as I know, the handling of NULL values in QV dos not always give the expected results when the Isnull function is used. So I would try using:

Len(Field)=0

Len(Trim(Field))=0

to check if avalue is null and the assign it the 'Unknown' value:

if(Len(Trim(Field))=0,'Unknown',Field) as...

Hope this helps.

Re: How do you assign values to nulls ?

Anonymous — Wed, 11 Dec 2013 10:09:56 GMT

While loading data try to assign some value to null values s

load

if(isnull(field_name),'Unknown',field_name) as field_name

from ............

Hope this will healp you aut.

Regards

Nitin

Re: How do you assign values to nulls ?

Wed, 11 Dec 2013 10:15:50 GMT

Hi Al On,

try to use this within a LOAD in script:

LOAD ...

if ( Len(Input) > 0, Input, ' -unknown- ')) AS Input,

....

Note:

- It is better to use Len() than check if a field is empty or null. This works not always properly.

- May be you want to use "Len(Trim(Input))" as a stronger version

- start your Defaultvalue with a blank; then it appears on top in Listboxes

Regards

Roland

Re: How do you assign values to nulls ?

qlikpahadi07 — Wed, 11 Dec 2013 10:52:10 GMT

Hi Al On

You can simply try this

Load

if(Len(Column) > 0,Column) as Column Name,

....

Re: How do you assign values to nulls ?

Wed, 11 Dec 2013 11:00:33 GMT

Hi, thanks for answering.

How would you modify the script to include that? Keep in mind I still want to use the conversion table in excel (I use it to fix spelling mistakes, merge similar categories, etc.)

[Lead Types Conversion Map]: //AL 11/12/13

Mapping

LOAD [Lead Type],

[Should Be]

FROM

[..\1_Resources\Lead_Types_Conversion_Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

Map [Lead Type] using [Lead Types Conversion Map];

Thanks for your help

Re: How do you assign values to nulls ?

Wed, 11 Dec 2013 11:01:12 GMT

thanks for answering

Please see my comment here:

http://community.qlik.com/message/435002#435002

Re: How do you assign values to nulls ?

Wed, 11 Dec 2013 11:01:18 GMT

thanks for answering

Please see my comment here:

http://community.qlik.com/message/435002#435002

Re: Re: How do you assign values to nulls ?

Anonymous — Wed, 11 Dec 2013 11:07:37 GMT

in mapping load only , you can use conditions to check with the null or length conditions .

Mapping

LOAD [Lead Type],

if(isnull( [Should Be]),'Unknown', [Should Be]) as [Should Be]

FROM

[..\1_Resources\Lead_Types_Conversion_Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

Mapping

LOAD [Lead Type],

if(len(trim( [Should Be]))=0,'Unknown', [Should Be]) as [Should Be]

FROM

[..\1_Resources\Lead_Types_Conversion_Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards

Nitin

Re: How do you assign values to nulls ?

salto — Wed, 11 Dec 2013 11:10:00 GMT

Hi,

try this:

[Lead Types Conversion Map]: //AL 11/12/13

Mapping

LOAD

if(Len(Trim([Lead Type]))=0,'Unknown',[Lead Type]) as [Lead Type],

[Should Be]

FROM

[..\1_Resources\Lead_Types_Conversion_Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

Map [Lead Type] using [Lead Types Conversion Map];

Hope this helps.

Re: How do you assign values to nulls ?

Wed, 11 Dec 2013 12:11:57 GMT

Hi,

thanks.

The bar chart still shows them as blanks, and they don't appear in the legend.

Re: How do you assign values to nulls ?

Anonymous — Wed, 11 Dec 2013 12:16:08 GMT

Have you tried my way..........????????

Re: How do you assign values to nulls ?

MayilVahanan — Wed, 11 Dec 2013 12:19:15 GMT

I thinks its not null values, its data mismatch when link with two tables.

Did you check the data model, what are the dimension and expression using, both dimension and expression fields occur in same table?, if same table, then the above suggestion will work definitely.

If it occurs in different table, then data mismatch.

Can you provide a sample ?

Re: How do you assign values to nulls ?

Wed, 11 Dec 2013 12:25:33 GMT

Hi,

the data is null in the origin (salesforce).

Re: How do you assign values to nulls ?

Wed, 11 Dec 2013 12:37:39 GMT

yes, same result

Re: How do you assign values to nulls ?

MayilVahanan — Wed, 11 Dec 2013 12:41:48 GMT

Can you provide a sample file?

Re: How do you assign values to nulls ?

Wed, 11 Dec 2013 12:44:28 GMT

Hi,

the data is confidential I'm afraid. I can generate an excel file with dummy data if that helps...

Re: How do you assign values to nulls ?

MayilVahanan — Wed, 11 Dec 2013 12:48:27 GMT

Yes

Re: How do you assign values to nulls ?

Anonymous — Wed, 11 Dec 2013 12:49:50 GMT

Mapping

LOAD [Lead Type],

if(len(trim( [Should Be]))=0 or isnull([Should Be]) ,'Unknown', [Should Be]) as [Should Be]

FROM

[..\1_Resources\Lead_Types_Conversion_Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards,

anant

Re: Re: How do you assign values to nulls ?

Wed, 11 Dec 2013 13:03:13 GMT

please see the attached. thanks!