Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
HI
PFA
It undoes the NullAsValue command, so it is basically just a backstop to prevent any weird behaviour later in the script.
Hi,
Can you please explain how this fixes the null issue? Seeing how you commented out the
//If(Len(Trim([Original Marketing Source])) = 0, 'Unknown', [Original Marketing Source]) AS [Original Marketing Source]
I had a conversion excel with null to 'Unknown' before and it didn't work. Your explanation would be very appreciated.
Thanks for all your help!
I see. Thanks. So how would the syntax look if I had multiple columns in the table, and more than 1 field could have null values? (e.g. 'marketing medium') ?
thanks!
The fields are just applied as comma separated values after the NullAsValue statement.
NullAsValue Field1, Field2, Field3;
or to cover all fields
NullAsValue *;
All described in the QlikView help files if you have them installed.
Hello Al On,
please check out the example app (with your data) showing a simple solution with applymap().
HtH
Roland
Hi Al On,
You can simply right If and Else conditions on any column you need :
Try This:
Table:
Table:
LOAD [Year-Month],
[Lead ID],
[Original Marketing Source],
if((isnull([Original Marketing Source])=-1 or
Trim([Original Marketing Source])='' or
len([Original Marketing Source])<=0),
'NULL',[Original Marketing Source]) as Original_Marketing_Source
FROM
[..\Desktop\dummy leads.xlsx]
(ooxml, embedded labels, table is Sheet2);
Find the attachment
beautiful! hooray!
Thanks everyone for posting and helping out! Eventually Roland's solution worked best. Sorry I can't give out more helpful solutions etc. You all deserve to be credited for your help! Thank you!
spoke too soon. it ONLY shows the unknown values now.