Date Format

[#1]

Hey guys.

I imported some data into qlikview but one of the primary date feilds format is in YYYY/MM/DD. i need it in DD/MM/YYYY.

I tried date(date#(AnniversaryDate,'YYYY/MM/DD'),'DD/MM/YYYY') but its not working. I get the following when i try apply a calendar:

Calendar script is as follows:

DateTemp:

LOAD %_AnniversaryDateKey

RESIDENT Data

ORDER BY %_AnniversaryDateKey Asc;

LET vMinDate = Num(Peek('%_AnniversaryDateKey', 0, 'DateTemp'));

LET vMaxDate = Num(Peek('%_AnniversaryDateKey', -1, 'DateTemp'));

LET vToday = date(date#(Today(),'DD/MM/YYYY'),'DD/MM/YYYY');

---------------------------------------------------------------------------------------------

TempCalendar:

LOAD

$(vMinDate)+IterNo()-1 AS Num,

Date($(vMinDate)+IterNo()-1) AS TempDate

AUTOGENERATE 1 WHILE ($(vMinDate)+IterNo()-1) <= $(vMaxDate);

--------------------------------------------------------------------------------

MasterCalendar:

LOAD TempDate AS %_AnniversaryDateKey,

week(TempDate) AS Week,

year(TempDate) AS Year,

yearname (TempDate, 0, 7) as FiscalYear,

month(TempDate) AS Month,

day(TempDate) AS Day,

weekday(TempDate) AS WeekDay,

applymap('Quarters_Map', num(month(TempDate)), null()) AS Quarter,

date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

week(TempDate)&'-'&Year(TempDate) AS WeekYear,

Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag

RESIDENT TempCalendar

ORDER BY TempDate Asc;

drop table TempCalendar,DateTemp;

[#2]

Also, if i take the DATE# function away and replace it with FLOOR function, it works but the years start from 1899.... when its supposed to start in 2000. 101 years off?... lol

[jonathandienst]

Blaze

The problem is in the AUTOGENERATE statement - QV doesn't like the comparison operator.

I think what you need to do is to create a variable with the AUTOGENERATE count in it, and then use this variable:

Let vDateCount = vMaxDate - vMinDate + 1;
...
AUTOGENERATE $(vDateCount);

Hope this helps

Jonathan

[#4]

Hey Jonathan

Thanks for the reply. The auto generate works fine I've used it in many other models. have you maybe got any other ideas?

Thanks again

[jonathandienst]

Blaze

You say it works fine but the error message you posted seems to say otherwise. For some reason QV is complaining about the <= symbol.

Jonathan

[#6 · Accepted solution]

Got it working. I thought id try a LEFT JOIN and it worked. Wat it was doing initially was creating doubles of the dates and in the end i had dates that i never needed. Then it autogenerated dates from years back because the format was incorrect. thanks anyways man.