Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kash04kk
Contributor II
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
jonathandienst
Partner - Champion III
Partner - Champion III

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

View solution in original post

11 Replies
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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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

Not applicable

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
Partner - Champion III
Partner - Champion III

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
Contributor II
Contributor II
Author

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.