topic Re: Shifting Calendardate in QlikView

Shifting Calendardate

Thu, 09 Apr 2015 11:12:41 GMT

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());

Re: Shifting Calendardate

pokassov — Thu, 09 Apr 2015 11:22:20 GMT

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.

Re: Shifting Calendardate

jonathandienst — Thu, 09 Apr 2015 11:28:49 GMT

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

Re: Shifting Calendardate

jonathandienst — Thu, 09 Apr 2015 11:31:26 GMT

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

Re: Shifting Calendardate

Thu, 09 Apr 2015 11:45:55 GMT

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.

Re: Shifting Calendardate

jonathandienst — Thu, 09 Apr 2015 11:49:36 GMT

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

Re: Shifting Calendardate

Thu, 09 Apr 2015 12:16:03 GMT

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?

Re: Shifting Calendardate

Thu, 09 Apr 2015 14:47:11 GMT

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