Problem with last date in IntervalMatch

[#1]

Hi,

I am trying to do an interval match using this function:

left join(1_tmp)

IntervalMatch (transaction_date)

LOAD

     distinct date(from_date) as from_date,

     date(to_date) as to_date    

FROM

[..x.QVD]

(qvd)

from_date and to_date are the first and last date in every month. transaction_date is also a date(transaction_date).

All records from 1_tmp get an from_date and end_date except from the last date in every month.  All idéas are welcome!

Reagrds

Anna

[qliksus]

You can give a condition in the transaction_date like

if ( monthend(transaction_date) = transaction_date , null() , transaction_date )  as transaction_date

so by this in the 1_temp table the monthend in the transaction_date will be eliminated

[#3]

Hi,

Thanks for the tip but I don't really see how that would help. Wouldn't that just remove the last day of the month and the result be that the records with a null date won't get any from_date or to_date? Or do I miss something?

Anna

[vincent_ardiet · Accepted solution]

Hi,

Are you sure that your transaction_date is not a timestamp containing hours ?

Try with :

left join(1_tmp)

IntervalMatch (transaction_date)

LOAD

     distinct date(from_date) as from_date,

     dayend(date(to_date)) as to_date   

FROM

[..x.QVD]

(qvd)

Regards,

Vincent

[#5]

Thanks, that helped!!