Date Format in QVDs not being recognized by Master Calendar

[#1]

I have an extract script to create a QVD of a Master Data base on the last day of every month so I can keep a historical record of the changes to that database. The creation of that QVD looks like this...

ODBC CONNECT TO JDE_9 (XUserId is XXX, XPassword is YYY);

Master:

Load*,

     MonthEnd(Today())     as     MonthEnd

;

SQL Select

     FACO,

     FADL01,

     FAEQST,

     FAMCU,

     FANUMB

From "Production".PRODDTA."Master";

When I load the QVD into a QVW and use the MonthEnd field for my Master Calendar, the data is no longer associated. I have attached both the QVD and QVW as a reference for help.

I will need the master calendar to associate the historical views of the master database to the transactions that occurred at that time as well. I expected to use a solution offered in the discussion link below by having a Master Data Base Calendar, and a Transaction Calendar.

http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/‌

Ultimately, I would like to understand how to get the date format correct. Using Date(MonthEnd) as Date did not work.

Thank you.

[sunny_talwar · Accepted solution]

Try adding the Floor function to your qvd load

AssetFile:

LOAD FACO,

    FANUMB,

    FADL01,

    FAEQST,

    FAMCU,

    MonthEnd,

    Date(Floor(MonthEnd)) as Date

FROM

(qvd);

[sunny_talwar]

[#4]

Thank you sir!