Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

1 Solution

Accepted Solutions
Not applicable
Author

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
Not applicable
Author

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

not tested sorry.

Not applicable
Author

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?

Not applicable
Author

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

;