topic Date Format in QlikView

Date Format

Fri, 17 Sep 2010 09:25:50 GMT

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;

Date Format

Fri, 17 Sep 2010 09:28:31 GMT

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

Date Format

jonathandienst — Fri, 17 Sep 2010 09:42:41 GMT

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

Date Format

Fri, 17 Sep 2010 09:45:21 GMT

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

Date Format

jonathandienst — Fri, 17 Sep 2010 09:49:38 GMT

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

Date Format

Fri, 17 Sep 2010 10:18:29 GMT

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.