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: 
easternfish
Contributor III
Contributor III

End of Month date reload

Hi,

I would like to reload data only for the last day of each month (end of month information).

Exp:

I would like my data to only read

1/31/18

2/28/18

3/31/18

etc...

thank you in advance.

8 Replies
sunny_talwar

May be this

LOAD ...,

     Date,

FROM ...

Where Date = Floor(MonthEnd(Date));

easternfish
Contributor III
Contributor III
Author

would this be correct? it won't load the data.

load..

Date (date(date#(DATE, 'YYYYMMDD'),'YYYY/MM/DD')) as EOM

FROM....

WHERE  EOM = Floor(MonthEnd(EOM));

sunny_talwar

This because EOM is not a field in this load... it will be a field after this load

LOAD..

     Date(Date#(DATE, 'YYYYMMDD'),'YYYY/MM/DD') as EOM

FROM....

WHERE  EOM = Floor(MonthEnd(Date#(DATE, 'YYYYMMDD')));

easternfish
Contributor III
Contributor III
Author

what is the definition of "Floor"? i'm trying it like this also and it wont load data.

LOAD..

     XDATE

FROM....

WHERE  XDATE = Floor(MonthEnd(Date#(XDATE, 'YYYYMMDD')));

zebhashmi
Specialist
Specialist

i think

Floor(MonthEnd(Date(XDATE, 'YYYYMMDD')))

would be fine

or

Floor(MonthEnd(Date(Date#(XDATE, 'YYYYMMDD'))))


sunny_talwar

MonthEnd gives a time stamp to the last second of the day....this isn't comparable to a month end date... so in order to make them equal... we need to use Floor (Floor essentially truncates the time portion from the timestamp)

easternfish
Contributor III
Contributor III
Author

thank you both for all the great information. !!!

Mimiii
Contributor
Contributor

Hello, 

maybe you can help me, how can I reload data for the last work day of each month in a variable? 

thank you for your help