Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to upload an Excel table with dates as column headers like this:
LeadCLRN | 16/04/2012 | 21/05/2012 | 18/06/2012 | 16/07/2012 |
BBC | 0.9289559 | 0.9989154 | 0.9869281 | 0.9798658 |
CDTV | 0.9888889 | 1 | 0.9895833 | 1 |
CEL | 0.8666383 | 0.8698805 | 0.8471872 | 0.8693861 |
CL | 0.9565217 | 0.8831169 | 1 | 1 |
CM | 0.9170654 | 0.9309764 | 0.9261745 | 0.9300813 |
And turn it into this in Qlikview:
LeadCLRN | Date | Value |
BBC | 16/04/2012 | 0.9289559 |
BBC | 21/05/2012 | 0.9989154 |
BBC | 18/06/2012 | 0.9869281 |
BBC | 16/07/2012 | 0.9798658 |
CDTV | 16/04/2012 | 0.9888889 |
CDTV | 21/05/2012 | 1 |
CDTV | 18/06/2012 | 0.9895833 |
CDTV | 16/07/2012 | 1 |
...etc
When I work through the Table Files wizard, the script that comes out just labels each date column as 'B', 'C', 'D' etc whereas I want this to show the actual date.
Temp:
CrossTable(Date, Value)
LOAD A as LeadCLRN,
B,
C,
D,
E
FROM
[CLRN Upload Engagement v3.xlsx]
(ooxml, explicit labels, header is 1 lines, table is Sheet1);
LeadCLRN | Date | Value |
BBC | B | 0.9289559 |
BBC | C | 0.9989154 |
BBC | D | 0.9869281 |
BBC | E | 0.9798658 |
CDTV | B | 0.9888889 |
CDTV | C | 1 |
CDTV | D | 0.9895833 |
CDTV | E | 1 |
I've looked through some other posts on here and they recommend doing a resident load on top of this and converting into date format, but if I do this all I get is blanks.
Temp:
CrossTable(Date, Value)
LOAD *
FROM
[CLRN Upload Engagement v3.xlsx]
(ooxml, explicit labels, header is 1 lines, table is Sheet1);
UpEng:
Load
A as LeadCLRN,
Date(Num#(Date)) as Date,
Value
Resident Temp;
Drop Table Temp;
LeadCLRN | Date | Value |
BBC | - | 0.9289559 |
BBC | - | 0.9989154 |
BBC | - | 0.9869281 |
BBC | - | 0.9798658 |
CDTV | - | 0.9888889 |
CDTV | - | 1 |
CDTV | - | 0.9895833 |
CDTV | - | 1 |
Can anyone help?
Thanks
Rory
Hi Mate,
I think the problem is with your headers when you load the data.
Follow these steps to ensure correct transformation
1) When in the EDIT SCRIPT click the 'Table Files' button in the DATA tab at the bottom
2) Navigate to your excel document and click open
3) This is the important part, make sure you click EMBEDDED LABELS in the dropdown 'LABELS' and click next
4) click next
5) Then click CROSSTABLE by prefixes
6) a dialog box will appear, change the Attribute field to 'Date' and data field to 'Value' and click ok
7) Click Finish. If by your fields you have columns pulling through with no data, then just exclude this from the script by deleting it.
Let me know if this fixed your problem
Cheers,
Byron
Hi Mate,
I think the problem is with your headers when you load the data.
Follow these steps to ensure correct transformation
1) When in the EDIT SCRIPT click the 'Table Files' button in the DATA tab at the bottom
2) Navigate to your excel document and click open
3) This is the important part, make sure you click EMBEDDED LABELS in the dropdown 'LABELS' and click next
4) click next
5) Then click CROSSTABLE by prefixes
6) a dialog box will appear, change the Attribute field to 'Date' and data field to 'Value' and click ok
7) Click Finish. If by your fields you have columns pulling through with no data, then just exclude this from the script by deleting it.
Let me know if this fixed your problem
Cheers,
Byron
Hi Byron
Thanks for your quick reply.
Yes, changing the label to Embedded Label does the trick - can't believe its that simple, I tried just about everything else!
When I first tried Embedded Labels, in the preview screen it chages the date to the basic date format (e.g. 44522) and changed my Value data to dd/mm/yyyy format, which didn't look right, so I didn't go with that option.
However, when you go and load the data up, the value data comes out normal (as a number, in my case) despite preview showing it in date format.
Thanks for your help, much appreciated.
Rory
see the attached file
hope it helps
when you fectching the data in labels you mention embeded label always
Pleasure mate. I promise you my biggest headaches with my reporting are usually the simplest things. It always helps to have an extra pair of eyes
Take care and all the best
Cheers,
Byron