topic Re: Exclude partial month data from Script Load? in QlikView

Exclude partial month data from Script Load?

Mon, 15 Dec 2014 03:44:26 GMT

I have data I'm importing from a QVD file that has multiple rows of data for each day, for example:

LOAD

Date,

Month,

Year,

Amount

FROM

data.qvd

(qvd);

This data has activity every day, so every date has data in it.

I'm trying to put in a WHERE clause to exclude the most recent month's worth of data but only if it's a partial month. That way I can load the data from my QVD with only full months' worth of data.

I was trying to use MonthEnd function, but cannot seem to get it right.

Thanks all.

Re: Exclude partial month data from Script Load?

Mon, 15 Dec 2014 04:07:39 GMT

where ( monthend(date_field)=today() and date_field <=today() ) or ( date_field <=date(floor(monthend(addmonths(Today(),-1))), 'YYYYMMDD'))

not tested sorry.

Re: Exclude partial month data from Script Load?

Mon, 15 Dec 2014 13:34:09 GMT

This did not work, but perhaps that is because you are using Today function and there is a lag in when I run this... in other word.... today might be 12/15/14, but the most recent activity date might be 12/6/2014... and I'm trying to exclude the partial month of December in this case..... so would I not use the Today function and use max(date) instead?

Re: Exclude partial month data from Script Load?

Mon, 15 Dec 2014 15:47:29 GMT

I think I got it. I replaced Today() in your code with a constant MaxDate value I created in a previous table, and it worked! Thanks!

where ( monthend(Date)=floor(peek('MaxDate',0,'MaxDateTable'))

and

Date <=floor(peek('MaxDate',0,'MaxDateTable')))

( Date <=date(floor(monthend(addmonths(floor(peek('MaxDate',0,'MaxDateTable')),-1))), 'YYYYMMDD'))

;