topic excel date format and Cross table in QlikView

excel date format and Cross table

brindlogcool — Thu, 19 Sep 2013 08:01:55 GMT

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

Re: excel date format and Cross table

Thu, 19 Sep 2013 08:24:35 GMT

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.

Re: excel date format and Cross table

Thu, 19 Sep 2013 08:37:33 GMT

Hi,

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

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;

Re: excel date format and Cross table

MK_QSL — Thu, 19 Sep 2013 08:50:32 GMT

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;