Can't format date in Load Script

[#1]

Hi guys,

I don't understand what is causing this problem. I have a date field which I extract from a SQL server using cast(date as date) function, which returns an ISO format YYYY-MM-DD.

In the Master Calendar autogeneration script, i'm trying to format the date like:

date(date#([Date], 'YYYY-MM-DD'),'DD/MM/YYYY') as Date.

I have a basic fact table, and a master calendar table for this particular date field, however, it is displaying in YYYY-MM-DD format. I've tried the above formula on the field in the fact table and in the master calendar, and it still displays incorrectly.

What's going on?

[#2]

Yes, I have changed the Set DateFormat to 'DD/MM/YYYY'.

I have managed to fix this problem for one of the date fields by just adding date(date#('Date', 'YYYY-MM-DD'), 'DD/MM/YYYY) in every occurance of Date in the load script.

However, I have two other dates where this solution makes no difference:

The data model is basically below. The real data model has a few more columns in the fact table, but this is essentially it.

[a_table]:

Load

ID

Activation_Date as [Activation Date],

Order_Date as [Order Date];

Select

ID,

cast(activation_Date as date) as Activation_Date,

cast(order_date as date) as Order_Date

from table;

//Variables

MinMax:

LOAD Min(date([Date], 'DD/MM/YYYY')) AS MinDate,

Max(date([Date], 'DD/MM/YYYY')) AS MaxDate

RESIDENT [a_table];

LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

///Calendars

ActivationCalendar:

Load

date(date#([Activation Date], 'YYYY-MM-DD'),'DD/MM/YYYY') as [Activation Date],

WeekName([Activation Date]) AS [Activation Week],

    [Activation Year],

    [Activation Month];

LOAD

Date($(vMinDate) + RecNo() - 1, 'DD/MM/YYYY') AS [Activation Date],

Year(Date($(vMinDate) + RecNo() - 1)) as [Activation Year],

Month(Date($(vMinDate) + RecNo() - 1)) as [Activation Month]

AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

//--- Do the same for OrderCalendar----

//--Try Canonical Date calendar suggestsed on Forums

ID2OrderDate:

MAPPING LOAD

ID,

date(date#([Order Date], 'YYYY-MM-DD'),'DD/MM/YYYY') as [Order Date]

RESIDENT [a_table];

ID2ActivatedDate:

MAPPING LOAD

ID,

date(date#([Activation Date], 'YYYY-MM-DD'),'DD/MM/YYYY') as [Activation Date]

RESIDENT [a_table];

DateBridge:

     Load ID, Applymap('ID2OrderDate',ID,Null()) as Date, 'Order' as DateType

          Resident [a_table];

     Load ID, Applymap('ID2ActivatedDate',ID,Null()) as Date, 'Activation' as DateType

          Resident [a_table];

GeneralCalendar:

Load

date(date#([Date], 'YYYY-MM-DD'),'DD/MM/YYYY') as Date ,

    [Month],

    [Year],

WeekName([Date]) AS [Week];

LOAD

Date($(vMinDate) + RecNo() - 1, 'DD/MM/YYYY') AS [Date],

Year(Date($(vMinDate) + RecNo() - 1)) as [Year],

Month(Date($(vMinDate) + RecNo() - 1)) as [Month]

//Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MMM-YYYY') AS [Order MonthYear]

AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

DROP TABLE MinMax;

[martinpohl]

Hello Natalie,

in the load script you load date(,'DD/MM/YYYY)

then you want to load this field with (date#(.'YYYY-MM-DD')

why?

You get date as DD/MM/YYYY so keep it.

BUT:

If the date field is load from your datas first the field will get the Format or your database.

Similiar numeric values are stored in the application in the syntax  they have been loaded the first time.

(05, 005 and 5 are stored as 05)

Regards

[#4]

So I took them all out to see whether it loaded as DD/MM/YYYY and it just went back to YYYY-MM-DD

[stigchel]

Try and move the conversion of dates from sql to qlikview, so

[a_table]:

Load

ID

Date(Floor(Activation_Date),'DD/MM/YYYY') as Activation_Date,

Date(Floor([Order Date]),'DD/MM/YYYY') as [Order Date];

Select

ID,

Activation_Date,

Order_Date

from table;