Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rory_mchugh
Not applicable

CrossTable Date Problem

I'm trying to upload an Excel table with dates as column headers like this:

LeadCLRN16/04/201221/05/201218/06/201216/07/2012
BBC0.92895590.99891540.98692810.9798658
CDTV0.988888910.98958331
CEL0.86663830.86988050.84718720.8693861
CL0.95652170.883116911
CM0.91706540.93097640.92617450.9300813

And turn it into this in Qlikview:

LeadCLRNDateValue
BBC16/04/20120.9289559
BBC21/05/20120.9989154
BBC18/06/20120.9869281
BBC16/07/20120.9798658
CDTV16/04/20120.9888889
CDTV21/05/20121
CDTV18/06/20120.9895833
CDTV16/07/20121

...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);

LeadCLRNDateValue
BBCB0.9289559
BBCC0.9989154
BBCD0.9869281
BBCE0.9798658
CDTVB0.9888889
CDTVC1
CDTVD0.9895833
CDTVE1

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;

LeadCLRNDateValue
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

1 Solution

Accepted Solutions
Not applicable

Re: CrossTable Date Problem

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

4 Replies
Not applicable

Re: CrossTable Date Problem

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
Not applicable

Re: CrossTable Date Problem

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
Not applicable

Re: CrossTable Date Problem

see the attached file

hope it helps

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

Not applicable

Re: CrossTable Date Problem

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