Please help me on null handling at set analysis?

[ananyaghosh]

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

[trdandamudi]

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)

[ananyaghosh · Accepted solution]

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