How do you assign values to nulls ?

[#1]

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!

[#2]

Hi,

thanks.

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

[Anonymous]

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

[MayilVahanan]

Hi

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 ?

[#5]

Hi,

the data is null in the origin (salesforce).

[#6]

yes, same result

[MayilVahanan]

Hi

Can you provide a sample file?

[#8]

Hi,

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

[MayilVahanan]

Yes

[Anonymous]

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

[#11]

please see the attached. thanks!