handling nulls

[joeybird]

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

[vinieme12]

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]

Hi

Use NullCount([SurveyName])

Regards

Pratyush

[joeybird]

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]

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]

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]

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]

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]

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]

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]