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.
kash04kk
New Contributor II

Loading multiple cross tables

Hello everyone,

I am trying to create multiple cross tables. I've an excel file with sheets 2009 and 2010. The header fields in both are same. What is wrong in the below script?? Thanks

for each vSheet in '2009', '2010'

CrossTable(Description, Data)

LOAD*

FROM

(ooxml, embedded labels, table is $(vSheet));

NEXT;

1 Solution

Accepted Solutions
MVP
MVP

Re: Loading multiple cross tables

I think that the problem is the crosstable. The first XL tab is loaded and then crosstabled. When the next tab is loaded and compared to the existing QV table and sees it as different, so it creates a second table, which it then cross tables.

What you need to do is load both tables without the crosstable, and then perform the crosstable to the resident table, like this:

For Each vSheet in '2009', '2010'

  T_Data:

  LOAD *

  FROM

  (ooxml, embedded labels, table is $(vSheet));

Next

CrossTable(Description, Data)

LOAD *

Resident T_Data;

Drop Table T_Data;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
11 Replies

Re: Loading multiple cross tables

What happens? Wrong results or an error message? Noticeable is the semi-colon behind the NEXT then control-statements like SUB / IF / FOR will be written without one. Further you load with a field-wildcard - are the sheets not absolute equal they won't be automatically concatenated - you will get two tables and by many identical fields a lot of synthetic keys which will need a lot of ressources to be calculated.

- Marcus

Re: Loading multiple cross tables

please post sample file to check with

regards

Marco

kash04kk
New Contributor II

Re: Loading multiple cross tables

Thanks Marcus for the response. There's no error in running the script but the cross table is made of only "2009" sheet. I want the cross tables for both 2009 and 2010 sheets.

kash04kk
New Contributor II

Re: Loading multiple cross tables

Sorry Marco. The file is of my office containing private information that i can't share. When i run the script, there are no errors but cross table of only 2009 is made. I want cross tables to be made for both 2009 and 2010 sheets.

Re: Loading multiple cross tables

How looks the log-file from load (enabling within the document-properties in tab general)? You could also use the debugger and/or TRACE-statements to see what is going on within the load-execution.

- Marcus

Re: Loading multiple cross tables

as we are interested in the format rather than the content of your file, maybe you could create some random data to work with.

thanks

regards

Marco

Not applicable

Re: Loading multiple cross tables

The same script worked for me. It loaded data from two sheets 2009 and 2010. Please check the sheet names for any typos in your excel file such as leading or trailing spaces etc. Just verify to load only sheet 2010 by changing for each statement like below

for each vSheet in '2010'

Though semicolon after NEXT may not give any error, it is not required.

Please let us know the output.

Regards,

KKR

MVP
MVP

Re: Loading multiple cross tables

I think that the problem is the crosstable. The first XL tab is loaded and then crosstabled. When the next tab is loaded and compared to the existing QV table and sees it as different, so it creates a second table, which it then cross tables.

What you need to do is load both tables without the crosstable, and then perform the crosstable to the resident table, like this:

For Each vSheet in '2009', '2010'

  T_Data:

  LOAD *

  FROM

  (ooxml, embedded labels, table is $(vSheet));

Next

CrossTable(Description, Data)

LOAD *

Resident T_Data;

Drop Table T_Data;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
kash04kk
New Contributor II

Re: Loading multiple cross tables

Thanks Jonathan. This worked.

But i notice that only 2010 is being converted as cross table i.e. only 2010 fields showing as Description and Data. 2009 fields are showing as they are.

Community Browser