Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

Exclude partial month data from Script Load?

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.

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Exclude partial month data from Script Load?

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')))

or

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

;

View solution in original post

3 Replies
Highlighted
Not applicable

Re: Exclude partial month data from Script Load?

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

not tested sorry.

Highlighted
Not applicable

Re: Exclude partial month data from Script Load?

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?

Highlighted
Not applicable

Re: Exclude partial month data from Script Load?

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')))

or

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

;

View solution in original post