Loading multiple cross tables

[kash04kk]

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;

[marcus_sommer]

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

[MarcoWedel]

please post sample file to check with

regards

Marco

[kash04kk]

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]

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.

[marcus_sommer]

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

[MarcoWedel]

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

[#8]

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

[jonathandienst · Accepted solution]

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;

[kash04kk]

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.