Shifting Calendardate

[#1]

Hi all!

Ive got a question regarding calendardates. In my database Ive got data related to a date, given as: 01.jan.2014.

When I import this directly into Qlikview it shows it as 01.jan.1942. So I have to compensate for that, as it is constantly the case for this specific ( ! ) field. We can come to the conclusion that it lacks 72 years behind the real value.

For other database tables this is sometimes different.

Question: How can I adjust this individually for certain fields so that it matches the real date?

My code:

SQL SELECT

registr,

departure_date AS CalendarDate,  (*NOW ADD X DAYS TO IT TO MATCH REAL TIME*)

daily_cycles,

FROM DATABASE X;

I use the following MasterCalender:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YY';

SET TimestampFormat='DD/MM/YY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LET vDateMin = Num(MakeDate(2013,1,1));

LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

  $(vDateMin) + RowNo() - 1 AS DateNumber,

  Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:

LOAD

    DateNumber AS CalendarDatenumber,

    TempDate AS CalendarDate,

    TempDate AS UtilizationDate,

    TempDate AS DelayDate,

    DAY(TempDate) AS CalendarDay,

    WEEKDAY(TempDate) AS CalendarWeekDay,

    WEEK(TempDate) AS CalendarWeek,

    MONTH(TempDate) AS CalendarMonth,

    YEAR(TempDate) AS CalendarYear,

    'Q' & CEIL(MONTH(TempDate)/3) AS CalendarQuarter,

    WEEKDAY(TempDate) & '-' & YEAR(TempDate) AS CalendarWeekDayAndYear,

    WEEK(TempDate) & '-' & YEAR(TempDate) AS CalendarWeekAndYear,

    MONTH(TempDate) & '-' & YEAR(TempDate) AS CalendarMonthAndYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

LET vDateMin = Num(MakeDate(2000,1,1));

LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

LET vDateToday = Num(Today());

[pokassov]

Hello!

I think it depends on the types of databases you use.

So I think it's the best way add compensation value in the sql select section.

[jonathandienst]

Perhaps you don't need to compensate. Try first to code the format into the Load so that the date is loaded correctly:

          LOAD

               ...

               Date(Date#(departure_date, 'dd.MMM.YYYY')) As departure_date,

               ...

HTH

Jonathan

[jonathandienst]

Its also possible that the dates in teh database are coded (for example a Julian date or the number of days from an arbitrary offset date). You will need to see what actually comes from that field. Test that with code like:

     LOAD Text(departure_date),

          Num(departure_date)

     FROM ....

[#5]

Helo thx for your response. I did the following now:

LOAD registr,

  Date(Date#(departure_date, 'dd.MMM.YYYY')) As departure_date,

    daily_cycles,

SQL SELECT registr,

  departure_date,

    daily_cycles,

FROM Database

It will not give me any input, the fields are now empty.

[jonathandienst]

Then it seems that the date is not in that format. See what you get with my second suggestion,. This is for testing and diagnosis only

[#7]

It shows it as a number now, 15342. So: 15342 / 365 days = 42 years.

So the starting date of the database is 1900, So this is what needs to be compensated for. But as you suggested maybe that is not neccesary. I would like to hear what to do about it in that case?

[#8 · Accepted solution]

I now did the following to shift the date:

LOAD registr,

  date(departure_date+26298) AS CalendarDate,

....

....

This works for this field. I will have to do this for other fields too and determine the right amount of days to shift...