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: 
byrnel0586
Creator
Creator

Date Load Issue

I am having problems with loading a date (5/31/2017). ReportDate is the field which is shared between the two tables, but 2 date values keep showing for 5/31/2017. It is like QV is recognizing one of the dates as something other than a date.

I am using these expressions to create the date fields:

Table 1:

Date(ReportDate,'M/D/YYYY') as ReportDate

Table 2:

Date(MonthEnd(AddMonths(P_ReportDate,-11)),'M/D/YYYY') AS ReportDate

The field shows 2 dates for 5/31/2017...

How can I fix this?

1 Solution

Accepted Solutions
prieper
Master II
Master II

Seems that you are not loading a date.

Did you try to load the date-field with DATE#(), e.g.

DATE(DATE#(Field2Read, 'M/D/YYYY'), 'YYYYMMDD')

So the inner would ask QV to read the field in the given format M/D/YYYY (or whatever it is). This will result technically in a number. The outer format then will ask QV to show this number as a date in the format YYYYMMDD (or any other, as per your choice).

View solution in original post

4 Replies
prieper
Master II
Master II

without seeing the real data, you may ensure that both dates have the same data-type (in this case a date without decimals (= hours)).

Table 1:

Date(FLOOR(ReportDate),'M/D/YYYY') as ReportDate

Table 2:

Date(MonthEnd(AddMonths(FLOOR(P_ReportDate),-11)),'M/D/YYYY') AS ReportDate


may do the trick already.

Else you may have to check the import-formats and use DATE# in order to force the reading in a different format than your common format.

Peter

byrnel0586
Creator
Creator
Author

Hi Peter,

Thank you, but when I add the FLOOR function the date disappears altogether...

prieper
Master II
Master II

Seems that you are not loading a date.

Did you try to load the date-field with DATE#(), e.g.

DATE(DATE#(Field2Read, 'M/D/YYYY'), 'YYYYMMDD')

So the inner would ask QV to read the field in the given format M/D/YYYY (or whatever it is). This will result technically in a number. The outer format then will ask QV to show this number as a date in the format YYYYMMDD (or any other, as per your choice).

byrnel0586
Creator
Creator
Author

Thank you. I ended up using a variation of the two and got it to work.