topic Re: Null value Invalid expression in QlikView

Null value Invalid expression

vadim_grab — Sat, 16 Nov 2024 21:42:49 GMT

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

Re: Null value Invalid expression

Nicole-Smith — Tue, 18 Dec 2018 21:19:07 GMT

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.

Re: Null value Invalid expression

vadim_grab — Wed, 19 Dec 2018 06:36:16 GMT

unfortunately did not help...

Re: Null value Invalid expression

sasikanth — Wed, 19 Dec 2018 07:22:26 GMT

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

Re: Null value Invalid expression

vadim_grab — Wed, 19 Dec 2018 08:49:08 GMT

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 *;

Re: Null value Invalid expression

sasikanth — Wed, 19 Dec 2018 09:56:27 GMT

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,

Re: Null value Invalid expression

vadim_grab — Fri, 21 Dec 2018 05:55:40 GMT

look like what i need...

Thanks!