Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vadim_grab
Creator
Creator

Null value Invalid expression

 Hi, All

I have a script expression

if(Len(Trim(ofddata_hour.fiscalSign))=0 or ofddata_hour.fiscalSign='-' or ofddata_hour.fiscalSign='' or IsNull(ofddata_hour.fiscalSign), Null(),Count(DISTINCT ofddata_hour.fiscalSign)) as fiscalSign

which don't work.

Script returns an error "Invalid expression"

Why? Any idea?

Many thanks,

Vadim

 

Labels (4)
1 Solution

Accepted Solutions
sasikanth
Master
Master

try below expression

count(DISTINCT  if(Len(Trim(ofddata_hour.fiscalSign))=0 or ofddata_hour.fiscalSign='-' or ofddata_hour.fiscalSign='' or IsNull(ofddata_hour.fiscalSign)=0, Null() , ofddata_hour.fiscalSign)) as fiscalSign,

View solution in original post

6 Replies
Nicole-Smith

What if you add a comparison for the IsNull?

if(Len(Trim(ofddata_hour.fiscalSign))=0 or ofddata_hour.fiscalSign='-' or ofddata_hour.fiscalSign='' or IsNull(ofddata_hour.fiscalSign)=-1, Null(),Count(DISTINCT ofddata_hour.fiscalSign)) as fiscalSign

Note: IsNull evaluates to -1 for true and 0 for false.

vadim_grab
Creator
Creator
Author

unfortunately did not help...

 

Script Error 2018-12-19 09.34.36.png

sasikanth
Master
Master

HI,

issue might be because  of Count function usage in the Script with out any aggregation.

try the same expression in any chart 

 

 

thanks, 

sasi

vadim_grab
Creator
Creator
Author

My script code below.

I used Group by .  With expression Count(DISTINCT ofddata_hour.fiscalSign) as fiscalSign 

instead of 

if(Len(Trim(ofddata_hour.fiscalSign))=0 or ofddata_hour.fiscalSign='-' or ofddata_hour.fiscalSign='' or IsNull(ofddata_hour.fiscalSign)=0, Null(),Count(DISTINCT ofddata_hour.fiscalSign)) as fiscalSign

data is loading...

 

ofddata_day:
Qualify *;
LOAD Distinct
DayStart(ofddata_hour.DateTimeBridge) as DateTimeBridge,
DayStart(ofddata_hour.DateTime) as DateTime,
DayStart(ofddata_hour.DateTime) as DateTimeStart,
DayEnd(ofddata_hour.DateTime) as DateTimeEnd,
ofddata_hour.operator as operator,
Sum(ofddata_hour.totalNds18118) as totalNds18118,
Sum(ofddata_hour.totalNds10110) as totalNds10110,
Sum(ofddata_hour.totalNds10) as totalNds10,
Sum(ofddata_hour.totalNds0) as totalNds0,
Sum(ofddata_hour.totalSum) as totalSum,
Sum(ofddata_hour.cashTotalSum) as cashTotalSum,
Sum(ofddata_hour.ecashTotalSum) as ecashTotalSum,
Sum(ofddata_hour.items_sum1) as items_sum,
Sum(ofddata_hour.totalSKU) as totalSKU,
Sum(ofddata_hour.uniqueSKU) as uniqueSKU,
Sum(ofddata_hour.totalQty) as totalQty,
// if(Len(Trim(ofddata_hour.fiscalSign))=0 or ofddata_hour.fiscalSign='-' or ofddata_hour.fiscalSign='' or IsNull(ofddata_hour.fiscalSign)=0, Null(),Count(DISTINCT ofddata_hour.fiscalSign)) as fiscalSign,
Count(DISTINCT ofddata_hour.fiscalSign) as fiscalSign,
ofddata_hour.bridge_aggr_key as bridge_aggr_key,
Sum(ofddata_hour.prepaidSum) as prepaidSum,
Sum(ofddata_hour.creditSum) as creditSum,
Sum(ofddata_hour.provisionSum) as provisionSum,
ofddata_hour.operationType as operationType,
ofddata_hour.AirportSales as AirportSales,
ofddata_hour.Bridge_key as Bridge_key
Resident ofddata_hour
Group by DayStart(ofddata_hour.DateTimeBridge), DayStart(ofddata_hour.DateTime), DayEnd(ofddata_hour.DateTime),ofddata_hour.operator, ofddata_hour.bridge_aggr_key, ofddata_hour.operationType, ofddata_hour.AirportSales, ofddata_hour.Bridge_key;
Unqualify *;

sasikanth
Master
Master

try below expression

count(DISTINCT  if(Len(Trim(ofddata_hour.fiscalSign))=0 or ofddata_hour.fiscalSign='-' or ofddata_hour.fiscalSign='' or IsNull(ofddata_hour.fiscalSign)=0, Null() , ofddata_hour.fiscalSign)) as fiscalSign,

vadim_grab
Creator
Creator
Author

look like what i need...

Thanks!