topic Re: Transponse Excel rows and Columns in QlikView

Transponse Excel rows and Columns

Wed, 25 Nov 2020 16:16:04 GMT

Hi all Qlik Guru's

I have a requirement where I need to load excel data and transpose the columns and rows. I used the wizard transformation enable step and did a rotate and transpose step in the wizard. The rows and columns I have is like below.

Date	product1	product2
29/02/2016	464952
01/03/2016	306618
02/03/2016	141	268843
03/03/2016	86	243516
04/03/2016	174	226639
05/03/2016	26394
06/03/2016	22491
07/03/2016	348	420167
08/03/2016	187	272833
09/03/2016	5172

But i want like this

Date	29/02/2016	01/03/2016	02/03/2016	03/03/2016	04/03/2016	05/03/2016	06/03/2016	07/03/2016	08/03/2016	09/03/2016
product1	141	86	174	348	187
product2	464952	306618	268843	243516	226639	26394	22491	420167	272833	5172

I'm getting the data like above, but unfortunately the dates are converting into numbers during load script like this: Bold one

LOAD Date as Channel_Desc,

[42429.000000],

[42430.000000],

[42431.000000],

[42432.000000],

[42433.000000],

[42434.000000],

[42435.000000],

[42436.000000],

[42437.000000],

[42438.000000]

my question is :

1) How can we retain the date column as dates instead of numbers.

2) Secondly how can I change the number to date column like above as final output

Your help is really needed. Please advse

Re: Transponse Excel rows and Columns

sunny_talwar — Thu, 10 Mar 2016 20:07:39 GMT

Fix the dates in the resident Load after you do your CrossTable Load

Table:

CrossTable(Date, Value)

LOAD Date as Channel_Desc,

[42429.000000],

[42430.000000],

[42431.000000],

[42432.000000],

[42433.000000],

[42434.000000],

[42435.000000],

[42436.000000],

[42437.000000],

[42438.000000]

FROM Excel;

FinalTable:

NoConcatenate

LOAD Channel_Desc,

Date(Num#(Date, '##')) as Date

Value

Resident Table;

DROP Table Table;

Re: Transponse Excel rows and Columns

Thu, 10 Mar 2016 20:32:21 GMT

Hi Sunny,

Thanks a lot. It worked