Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Not applicable
Author

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
marcioarruda
Creator III
Creator III

Try this in table 3:

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

Not applicable
Author

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!

muthukumar77
Partner - Creator III
Partner - Creator III

Hi,

Can you send your data model?

Muthukumar Pandiyan
Not applicable
Author

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.