Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
easternfish
New 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.

7 Replies

Re: End of Month date reload

May be this

LOAD ...,

     Date,

FROM ...

Where Date = Floor(MonthEnd(Date));

easternfish
New Contributor III

Re: End of Month date reload

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

Re: End of Month date reload

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
New Contributor III

Re: End of Month date reload

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
Valued Contributor

Re: End of Month date reload

i think

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

would be fine

or

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


Re: End of Month date reload

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
New Contributor III

Re: End of Month date reload

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