Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

Tags (1)
1 Solution

Accepted Solutions

Re: Problem loading Excel data from a crosstable

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

2 Replies

Re: Problem loading Excel data from a crosstable

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

Not applicable

Problem loading Excel data from a crosstable

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

Community Browser