topic Expression to include null values in QlikView

Expression to include null values

Thu, 14 Oct 2010 15:41:25 GMT

Hi ,

I have below expression which should include both null values and current_R for status_comp_Retail field.But when am using using the below expression it is only displaying current_R values for sttaus_comp_Retail but not null values.Please suggest.

Sum({$<Store_fiscal=(P({<status_comp={Current}>})),status_comp={Current},status_comp_Retail={},status_comp_Retail={Current_R},
status={Current}>} if(Store_fiscal='0099' or Store_Retail='0099' or noncomp_fiscal='0099' or noncomp_retail='0099','0', if(Coupon_Flag='Y' and ACCT='MHOODC' or 'QOP.C' ,aggr(sum(Sold_At*Qty*CONV_RATE),Order_Key)-Coupon_Dollars,
if(Coupon_Flag='Y' and ACCT<>'MHOODC' or 'QOP.C' ,aggr(sum(Sold_At*Qty),Order_Key)-Coupon_Dollars,
if(Coupon_Flag<>'Y' and ACCT='MHOODC' or 'QOP.C' ,(Sold_At*Qty*CONV_RATE),
(Sold_At*Qty))))))

Thanks,

Swetha

Expression to include null values

Thu, 14 Oct 2010 16:50:41 GMT

Hi,

in my experience is not possible to work with null values within set analysis, however you can try the following and see if it works for you:

status_comp_Retail={} + {Current_R}

If the above does not work, you can either use an sum(if) like expression to validate if status_comp_Retail is null or substitute null values within load script for numeric or text flags so you can filter with set analysis.

Regards

Expression to include null values

Thu, 14 Oct 2010 18:05:25 GMT

Thanks Ivan,

I have used the sugestion given by you but its not working.The problem is actually there are no null values in the table so when am selecting bull values it just says 0.I am getting null only when there is no match with other fields in the data model.Please suggest how to overcome this issue.Is there a way in set analysis?

Thanks,

Swetha

Expression to include null values

Thu, 14 Oct 2010 18:11:10 GMT

Hi Everyone,

Is there a way to exclude certain values in set nalysis?Pls suggest

Expression to include null values

Thu, 14 Oct 2010 18:27:33 GMT

Probably I can be more helpful, if you provide us with an example qvw that replicates your issue.

Regards

Expression to include null values

Thu, 14 Oct 2010 18:32:19 GMT

Pls find the enclosed doc which illustrates teh example.

Expression to include null values

Thu, 14 Oct 2010 20:41:03 GMT

Hi Guys,

Any suggestions after seeing the doc.I would have include both the total-5399+39795.Is it possible at all in qlikviw using set analysis.Any help would be appreciated.

Thanks.

Expression to include null values

sathishkumar_go — Fri, 15 Oct 2010 09:25:41 GMT

HI Swetha,

Can u attach ur application here?

Regards

Sathish