Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

joeybird
Contributor II

handling nulls

Hi

loading in two SQL tables

Table 1 - Survey - Names the surveys the customer has completed

Table 2 - Main - Main Customer Table

when I load in these two sql tables, some CustomerID wont have completed a survey, and survey name appears as null - as expected

I have working Count(if([SurveyName]=0,[SurveyName])) on a table chart. , this limits the CustomerIDs that have not completed a survey

However I want to be able to count how many CustomerID, that have a Survey Name as null in a KPI

with something like

Count(if([SurveyName]=0,[SurveyName])) CustomerID)

but it does not work

I have tried too replace the nulls so they are selectable with no joy

SET NullValue = 'No Survey'

NullAsValue "SurveyName"

please help


14 Replies
vinieme12
Esteemed Contributor II

Re: handling nulls

add a flag to your null values during load

For example

if(isnull(SuveyName),'NA',SurveyName) as SurveyName

Then use Expression

Count({<SurveyName = {'NA'}>} CustomerID)

prat1507
Valued Contributor

Re: handling nulls

Hi

Use NullCount([SurveyName])

Regards

Pratyush

joeybird
Contributor II

Re: handling nulls

hi

if(isnull(SuveyName),'NA',SurveyName) as SurveyName

this does not work because SurveyName column does not appear in the "Main" Table

please help


vinieme12
Esteemed Contributor II

Re: handling nulls

Sorry; but i assume you CustomerID and Survey Tables are associated with each other?

if both the tables are associated , I don't see why this would not work

vinieme12
Esteemed Contributor II

Re: handling nulls

Yes , Nullcount() is an option but then we can't see Null values in a listbox....so incase when we want to immediately filter all customers who have not given a survey we need to replace null values with a flag

prat1507
Valued Contributor

Re: handling nulls

Yes for that we've to use the method as suggested by you however since the lady asked for count, this was the simplest way I could think of. But I feel she has still not figured out as to what she wants to achieve, it will be great if joanna could share the app or a sample app.

Regards

Pratyush

joeybird
Contributor II

Re: handling nulls

Hi

the only link the tables have is both have the column Customer ID

I have tried concatenating the tables , to get code to work but still error

please help

joeybird
Contributor II

Re: handling nulls

Hi

NullCount([SurveyName])

for this piece of code I only get 0 for the KPI

I Have also tried Count({$<[SurveyName] = ISNULL >}CustomerID)

no joy

please help


vinieme12
Esteemed Contributor II

Re: handling nulls

what is the error that you are getting?

Does the Survey Name field actually has nulls or blank spaces?

try

if(len(trim(([Suvey Name]))>0,'NA',[Survey Name]) as [Survey Name]

Community Browser