4 Replies Latest reply: May 9, 2013 7:29 AM by Byron Van Wyk RSS

    CrossTable Date Problem

    Rory McHugh

      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

        • Re: CrossTable Date Problem
          Byron Van Wyk

          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

            • Re: CrossTable Date Problem
              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

            • Re: CrossTable Date Problem
              Mohit Sharma

              see the attached file

              hope it helps

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