topic Re: handling nulls in App Development

handling nulls

joeybird — Fri, 10 Feb 2017 12:16:21 GMT

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

Re: handling nulls

vinieme12 — Fri, 10 Feb 2017 12:21:55 GMT

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)

Re: handling nulls

prat1507 — Fri, 10 Feb 2017 12:24:59 GMT

Use NullCount([SurveyName])

Regards

Pratyush

Re: handling nulls

joeybird — Fri, 10 Feb 2017 12:32:26 GMT

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

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

please help

Re: handling nulls

vinieme12 — Fri, 10 Feb 2017 12:37:21 GMT

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

Re: handling nulls

vinieme12 — Fri, 10 Feb 2017 12:38:39 GMT

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

Re: handling nulls

prat1507 — Fri, 10 Feb 2017 12:54:45 GMT

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

Re: handling nulls

joeybird — Fri, 10 Feb 2017 13:06:58 GMT

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

Re: handling nulls

joeybird — Fri, 10 Feb 2017 13:12:36 GMT

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

Re: handling nulls

vinieme12 — Fri, 10 Feb 2017 13:12:42 GMT

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]

Re: handling nulls

joeybird — Fri, 10 Feb 2017 13:37:44 GMT

this don't work either

says "SurveyName" field not found

survey Name field does not appear in both tables

please help

Re: handling nulls

vinieme12 — Fri, 10 Feb 2017 13:39:37 GMT

can you post your script?

Re: handling nulls

vinieme12 — Fri, 10 Feb 2017 13:40:35 GMT

or some sample app or sample data?

Preparing examples for Upload - Reduction and Data Scrambling

Re: handling nulls

joeybird — Fri, 10 Feb 2017 13:48:35 GMT

I cant but here are some sample tables

SQL Table Survey:

CustomerID SurveyName

1 Customer Service

3 Customer Service

4 Customer Service

5 Customer Service

SQL Table Main

CustomerID Name Surname

1 John Smith

2 Joe Bloggs

3 Jane Williams

4 Sam Morgan

5 Liz Smith

Should Return

CustomerID Name Surname SurveyName

1 John Smith Customer Service

2 Joe Bloggs Null

3 Jane Williams Customer Service

4 Sam Morgan Customer Service

5 Liz Smith Customer Service

null count in KPI should be 1 , but no joy

please help

Re: handling nulls

vinieme12 — Fri, 10 Feb 2017 14:04:04 GMT

Check the Attached, you can now filter customers with no survey

temp:

Load * inline [

CustomerID,SurveyName

1,Customer Service

3,Customer Service

4,Customer Service

5,Customer Service

];

JOIN

Load * Inline [

CustomerID,Name,Surname

1,John Smith

2,Joe Bloggs

3,Jane Williams

4,Sam Morgan

5,Liz Smith

];

NoConcatenate

FACT:

LOAd * ,

if(isnull(SurveyName),'NA',SurveyName) as NewSurveyName Resident temp;

Drop Table temp;