topic Re: Problem loading Excel data from a crosstable in QlikView

Problem loading Excel data from a crosstable

Tue, 30 Aug 2011 10:50:51 GMT

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 — Tue, 30 Aug 2011 10:58:11 GMT

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

Problem loading Excel data from a crosstable

Tue, 30 Aug 2011 11:09:42 GMT

Thanks Miguel,

I still couldn't get the crosstable to load with that code, but the resident option you recommended will work nicely for me.

Cheers!

Nick