Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
byrnel0586
Contributor

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
Honored Contributor II

Re: Date Load Issue

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

4 Replies
prieper
Honored Contributor II

Re: Date Load Issue

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
Contributor

Re: Date Load Issue

Hi Peter,

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

prieper
Honored Contributor II

Re: Date Load Issue

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
Contributor

Re: Date Load Issue

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

Community Browser