topic Problem with last date in IntervalMatch in QlikView

Problem with last date in IntervalMatch

Thu, 31 May 2012 09:06:42 GMT

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

Problem with last date in IntervalMatch

qliksus — Thu, 31 May 2012 15:04:22 GMT

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

Problem with last date in IntervalMatch

Thu, 31 May 2012 15:23:13 GMT

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

Problem with last date in IntervalMatch

vincent_ardiet — Thu, 31 May 2012 15:28:17 GMT

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

Problem with last date in IntervalMatch

Thu, 31 May 2012 15:48:59 GMT

Thanks, that helped!!