CrossTable Date Problem

[rory_mchugh]

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

[#2 · Accepted solution]

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

[rory_mchugh]

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

[er_mohit]

see the attached file

hope it helps

when you fectching the data in labels you mention embeded label always

[#5]

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