Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Hi Sunny,
Thanks a lot. It worked