Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Try this in table 3:
date(max(datefield),'MM-DD-YYYY') AS MaxDate
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!
Hi,
Can you send your data model?
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.