Master Calendar ignored certain dates in the date field

[poklegoguy]

Hi, I am currently having issues with dates. Here's my scenario, I have 2 different datasets, one is stored into a QVD and another one is in Excel. Since both of the datasets have similar columns, I decided to just concatenate them into one and linked the date field to the master calendar. To my surprise, all the dates and other data are linked properly for the data from QVD but not the for the data from the Excel file. I've made sure they are in the same format which is 'DD/MM/YYYY' but the dates from Excel are not being associated to the Master Calendar. Some details is that the dates in Excel are set to 'Custom' format which is 'DD/MM/YYYY hh:mm' and I had formatted them in the load script with functions, Date(TimeStamp#([DateField], 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY) and it is still not working. I have checked the concatenated table and the dates are properly concatenated into a single date column and are in the same format. Can anyone enlighten me what might be wrong? Appreciate any help!

[seanbruton]

Try this solution....

This can be a common problem.

1. Make both dates into a text then using date(date#()) to convert them to a date. I would also to save space and for speed later wrap that expression with num (). This will give you a clean serial date like 45789. Then you can use Qliks Datefunctions on the front end to do anything.

Regards

[poklegoguy · Accepted solution]

After trying various of transformations and functions, I somehow make it worked by using Date(Floor([DateField])) functions.