Date format

[Anonymous]

Hi!

I have a problem with changing date format.

I have an Excel file with a crosstable which looks like this:

Field1	Field2	Field3	Date1	Date2	Date3	Date4	Date5
a1	b1	c1	Amount	Amount	Amount	Amount	Amount
a2	b2	c2	Amount	Amount	Amount	Amount	Amount
a3	b3	c3	Amount	Amount	Amount	Amount	Amount

The date format in Excel is 'DD.MM.YYYY', but when loading it in Qlik with crosstable load it changes to 5 numbers (if I understand it correctly the number represents the Amount of days starting from the min date stored in Qlik).

I think I have tried every possible way to change the format back to 'DD.MM.YYYY', but every time its just a blank.

Here is the script:

LET vMinDate = num(MakeDate(1899,12,30));

temp:
CrossTable(Date, Amount, 3)
LOAD Field1,
Field2,
Field3,
[41362],
[41785],
[42102],
[42365],
[42845]
FROM
$(vDataPath)\file.xlsx
(ooxml, embedded labels, header is 1 lines, table is aaa);

temp2:
LOAD Field1,
Field2,
Field3,
//Date(Date#(Date, 'DD.MM.YYYY'), 'DD.MM.YYYY') as Date,
   //Date(floor(Date#(Date)), 'DD.MM.YYYY') as Date,
   //Date(floor(Date), 'DD.MM.YYYY') as Date,
   //Date(num(Date), 'DD.MM.YYYY') as Date,
   //Date(Date) as Date,
   //Date('$(vMinDate)' + num(Date)) as Date,
   Date(MakeDate(1899,12,30) + Date) as Date,

DROP Table temp;

Thank you in advance.

[its_anandrjs · Accepted solution]

Try this way

LET vMinDate = num(MakeDate(1899,12,30));

temp:
CrossTable(Date, Amount, 3)
LOAD Field1,
Field2,
Field3,
[41362],
[41785],
[42102],
[42365],
[42845]
FROM
$(vDataPath)\file.xlsx
(ooxml, embedded labels, header is 1 lines, table is aaa);

temp2:
LOAD

Field1,
Field2,
Field3,

Date(Num(Date),'DD.MM.YYYY') as Date

Resident temp;

DROP Table temp;

For date conversion try some of this as well for your format

Num(Date) as Date

Or

Date(Num(Date),'DD.MM.YYYY') as Date

Or

Date(Num#(Date,'#####'),'DD.MM.YYYY') as Date

[kenphamvn]

Hi

Please show data of column [Date] in table temp

i'm using

date(Date,'DD.MM.YYYY') as FormatDate

it work well with  script bellow

temp:
 LOAD * INLINE [
     Date
     41362
     41785
     42102
     42365
     42845
 ];

 temp1:
 load * ,date(Date,'DD.MM.YYYY') as FormatDate
 Resident temp;

 Drop Table temp;

[Anonymous]

Date(Num#(Date,'#####'),'DD.MM.YYYY') as Date worked perfectly.

[its_anandrjs]

Good Work

[divya_shinde]

Hii,

Date(num#(Date),'DD.MM.YYYY')

It works!!!