Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ananyaghosh
Contributor III

Please help me on null handling at set analysis?

Hi,

if(not isNULL(JMS_DELIVERED_DATE_TIME) and not isNULL(FIRST_TOUCH_DATE_TIME),

Count({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"}>}TRANSACTION_RECORD_KEY), 0)

giving me 0 values , but when I am going to use below sql:

select count( TRANSACTION_RECORD_KEY) as Transaction_Count

from edi_e2e_user.abc_e2e_tracking_record where

UNPACKAGED_DATE_TIME is not null and FIRST_TOUCH_DATE_TIME is not null and      

CAST(FROM_TZ(CAST(UNPACKAGED_DATE_TIME AS TIMESTAMP), 'UTC') at time zone 'America/New_York' AS Timestamp) between

TO_TIMESTAMP('22-AUG-18 12.00.00.000000000 AM', 'DD-Mon-YY HH.MI.SS.FF9 AM') and

TO_TIMESTAMP('22-AUG-18 11.59.59.000000000 PM', 'DD-Mon-YY HH.MI.SS.FF9 AM');

I am getting 11178 rows, then please advise me how to use it in set expression, or if anything is wrong in my above code , please correct me

Tags (1)
1 Solution

Accepted Solutions
Highlighted
ananyaghosh
Contributor III

Re: Please help me on null handling at set analysis?

Hi,

Yor expression is not working for me when I am using it at KPI. But I am using the below expression and it works for me:

=Count({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"},

JMS_DELIVERED_DATE_TIME -={'=Len(Trim(JMS_DELIVERED_DATE_TIME))=0'}, FIRST_TOUCH_DATE_TIME -={'=Len(Trim(FIRST_TOUCH_DATE_TIME))=0'}>}TRANSACTION_RECORD_KEY)

Thanks,

Sandip

2 Replies
trdandamudi
Honored Contributor II

Re: Please help me on null handling at set analysis?

First try to split the expressions and see if you are getting any values. Regarding NULLs see if the below will help:

if(Len(Trim(JMS_DELIVERED_DATE_TIME)) >0 and Len(Trim(FIRST_TOUCH_DATE_TIME))>0,

Count({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"}>}TRANSACTION_RECORD_KEY), 0)

Highlighted
ananyaghosh
Contributor III

Re: Please help me on null handling at set analysis?

Hi,

Yor expression is not working for me when I am using it at KPI. But I am using the below expression and it works for me:

=Count({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"},

JMS_DELIVERED_DATE_TIME -={'=Len(Trim(JMS_DELIVERED_DATE_TIME))=0'}, FIRST_TOUCH_DATE_TIME -={'=Len(Trim(FIRST_TOUCH_DATE_TIME))=0'}>}TRANSACTION_RECORD_KEY)

Thanks,

Sandip