2 Replies Latest reply: Aug 30, 2011 7:09 AM by Nick Swift RSS

    Problem loading Excel data from a crosstable

      Hi,

       

      I'm having a problem trying to load data in via a crosstable, so would appreciate some ideas on this one!  Using QV10 SR1.

       

      Via the table wizard, I generate the following script code.  This loads and works fine (I've added the header "Profiles" of course to the table).

       

      Profiles:

      CrossTable(MasterDate, ProfileValue, 6)

      LOAD * FROM

      WP_ProfileTest.xlsx

      (ooxml, embedded labels, table is Sheet1);

       

      However, I want to do some other stuff in the script to rename a couple of fields and load in dates in a slightly different way.  Here's an example of what I'm trying to do.

       

      Profiles:

      CrossTable(MasterDate, ProfileValue, 6)

      LOAD Programme,

        Flow,

        District,

        Centre as TR_Office,

        Company as TR_Company,

        Description as Profile_Description

      FROM

      WP_ProfileTest.xlsx

      (ooxml, embedded labels, table is Sheet1);

       

      However, when I do this and reload, Qlikview just appears to hang and sits there forever.  If I put "LOAD *," followed by the fields, it does complete but doesn't then load the other fields at all.

       

      I have no idea why it's doing this or what it is doing!  Any ideas much appreciated.

       

      Nick

        • Re: Problem loading Excel data from a crosstable
          Miguel Angel Baeyens de Arce

          Hi Nick,

           

          When it comes to crosstable loads, I always do one of both

          • Do the crosstable load using * and then a second load resident from the previous renaming and changing
          • Use the Transformation Step to change what you need to change and then do the crosstable load

          Anyway, taking a look at your code, you are using the frist 6 columns (those taht are not going to be cross loaded) but you don't add in the LOAD part those columns that correspond to the MasterDate and ProfileValue, so my guessis that your script should look like

           

          Profiles:
          CrossTable(MasterDate, ProfileValue, 6)
          LOAD Programme,
            Flow,
            District,
            Centre as TR_Office,
            Company as TR_Company,
            Description as Profile_Description,
            // Rest of the columns here
            // If there are too much, then let the wizard write them for you
          FROM
          WP_ProfileTest.xlsx
          (ooxml, embedded labels, table is Sheet1);
          

           

          Hope that helps.

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica