Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

Issue with dates

Hello fellow Qlikviewers,


I'm having some trouble with one of my dates within the loading script

Mu quest is a search for the max of dates of 3 tables.

I'm from the Netherlands so that means we normally use a dateformat as DD-MM-YYYY.

Apparently my Database cannot handle that input.

So after a lot of trial and error i noticed that MM-DD-YYYY was working well.

I'm using the dateformat:  SET DateFormat='MM-DD-YYYY';

All Max dates are loaded as: Load DATE(max(datefield)) as MaxDate

Now my issue:

In table 1 i get a correct date for today:          16-12-2016

In table 2 i also get the same result:              16-12-2016

However, in table 3, my date is shown as:     05-31-2047

I'm using the exact same date format, the same database and the same load script..

At this moment i am Clueless!

Anyone has a bright idea?

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Issue with dates

Hi Muthukumar,

My problem is not relevant anymore.

The string Marcio posted  helped me resolve the problem at hand.

When using this string and mixing up the dates a bit MM-DD // DD-MM // D-M // M-D i noticed that the output did not change at all.

This problem was created by a user who had made some sales lines in 2007 but put in the date 31-05-2047 instead of 31-5-2007. This wrong date has never been changed and now it is stuck in our database because it is historic data.

I've joined this table with another one and used that (correct) date. Issue within Qlikview solved.

View solution in original post

4 Replies
Contributor III

Re: Issue with dates

Try this in table 3:

date(max(datefield),'MM-DD-YYYY') AS MaxDate

Highlighted
Not applicable

Re: Issue with dates

Thanks, it was not the specific solution to fix my problem but it turned me in a direction where i found the problem at hand!

Highlighted
Partner
Partner

Re: Issue with dates

Hi,

Can you send your data model?

Muthukumar Pandiyan
Highlighted
Not applicable

Re: Issue with dates

Hi Muthukumar,

My problem is not relevant anymore.

The string Marcio posted  helped me resolve the problem at hand.

When using this string and mixing up the dates a bit MM-DD // DD-MM // D-M // M-D i noticed that the output did not change at all.

This problem was created by a user who had made some sales lines in 2007 but put in the date 31-05-2047 instead of 31-5-2007. This wrong date has never been changed and now it is stuck in our database because it is historic data.

I've joined this table with another one and used that (correct) date. Issue within Qlikview solved.

View solution in original post