topic Re: Loading multiple cross tables in QlikView

Loading multiple cross tables

kash04kk — Sun, 28 Jun 2015 07:15:13 GMT

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;

Re: Loading multiple cross tables

marcus_sommer — Sun, 28 Jun 2015 07:31:29 GMT

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

MarcoWedel — Sun, 28 Jun 2015 10:13:28 GMT

please post sample file to check with

regards

Marco

Re: Loading multiple cross tables

kash04kk — Sun, 28 Jun 2015 10:16:21 GMT

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.

Re: Loading multiple cross tables

kash04kk — Sun, 28 Jun 2015 10:19:27 GMT

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

marcus_sommer — Sun, 28 Jun 2015 11:09:01 GMT

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

MarcoWedel — Sun, 28 Jun 2015 16:01:07 GMT

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

Re: Loading multiple cross tables

Mon, 29 Jun 2015 05:03:05 GMT

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

Re: Loading multiple cross tables

jonathandienst — Mon, 29 Jun 2015 05:21:29 GMT

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));

CrossTable(Description, Data)

LOAD *

Resident T_Data;

Drop Table T_Data;

Re: Loading multiple cross tables

kash04kk — Tue, 30 Jun 2015 06:52:01 GMT

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.

Re: Loading multiple cross tables

jonathandienst — Tue, 30 Jun 2015 07:26:46 GMT

Check the table viewer (Ctrl-T) after the load. If the sheets have any differences in the fields (and remember that field names in QV are case-sensitive) then the second table will not be auto-concatenated. Look for a table named T_Data1 or T_Data_1.

Re: Loading multiple cross tables

kash04kk — Tue, 30 Jun 2015 07:34:24 GMT

the sheets have the same fields. after load the table viewer looks like this: