excel date format and Cross table

[brindlogcool]

We are using the Excel as the Data Source. It is in format as

Product Id  01/01/2013 01/07/2013 01/11/2013 01/18/2013

   1                  1                3                 6                8

   2                  2                32                67             3

   2                  3                2                 7                8

I want to convert to

Product Id        Date              Data

    1            01/01/2013           1

    1            01/07/2013           3

    1            01/11/2013           6

    1            01/18/2013           8

    2            01/01/2013           2

    2            01/07/2013           32

    2            01/11/2013           67

    2            01/18/2013           3

    2            01/01/2013           3

    2            01/07/2013           2

    2            01/11/2013           7

    2            01/18/2013           8

But the problem is in Qlikview the Excel date is considered as numbers as it below and i tried most of date function but it is not working.

Product Id        Date              Data

    1             [41234]               1   

    1             [41235]               3

    1             [41232]               6

    1            [41231]                8

    2            [41236]                2

    2            [41237]                32

    2            [41238]                67

    2            [41224]                3

    2           [41214]                 3

    2            [41224]                2

    2           [41239]                 7

    2            [41254]                8

I can add each Column header with date excluding the Square bracket it will work fine.But the problem number of dates will be keep changing i cannot hardcord it. And i will be using cross tables.

Is there any way i can fix excel date formatting

[#2]

Hi,

Temp:

CrossTable(DateNum, Data, 2)

LOAD Data,

[41234],

[41235] ,

[41232],

[41231]

FROM

[Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

DateTable:

LOAD

  Data,

  Num#(DateNum, '#') AS DateNumber

Resident Temp;

Try this.

[#3]

Hi,

Use this Script..................

A:

CrossTable(Date,Data,1)
LOAD ProductID,
[41536],
[41537],
[41538],
[41539],
[41540],
[41541]
FROM
C:\Users\qvadmin\Desktop\Book1.xlsx
(ooxml, embedded labels, table is Sheet1);

(add square bracket to your date field column)

NoConcatenate

B:
LOAD ProductID,
Date(trim(Date),'DD/MM/YYYY') AS Date,
Data
Resident A;

DROP Table A;

[MK_QSL]

TEMP:
CrossTable(Date, Data, 1)
LOAD ProductID,
all your date….....

FROM .....

FINAL:

NoConcatenate
load
ProducID,

date(num#(Date)) as Date
Resident TEMP;
drop table TEMP;