topic Re: Cross-Table load from Excel files in QlikView

Cross-Table load from Excel files

nagaiank — Sun, 22 May 2016 22:20:13 GMT

Hello, I was not able to find a solution in the forum to the following scenario.

In order load three month sales data from Excel using CrossTable prefix, the data file and script are below:

Sales:

CrossTable(Month, Sales)

LOAD Region,

[42370],

[42401],

[42430]

FROM ...

If the fourth month data is added, the script has to be changes to

Sales:

CrossTable(Month, Sales)

LOAD Region,

[42370],

[42401],

[42430],

[42461]

FROM ...

Is there a way to arrive at a script which will detect the number of months for which the data exists in the Excel file and load data for all months, not requiring to modify script every month?

Thanks in advance for your help.

Re: Cross-Table load from Excel files

swuehl — Sun, 22 May 2016 22:32:16 GMT

If the order of your fields in the input table is Region, then all Month fields, you should be fine with

Sales:

CrossTable(Month, Sales)

LOAD *

FROM ...

Re: Cross-Table load from Excel files

nagaiank — Sun, 22 May 2016 22:41:52 GMT

Thank you Stefan. It worked.

Re: Cross-Table load from Excel files

Gabriel — Sun, 22 May 2016 23:13:51 GMT

Hi,

I agree with Stefan's suggestion. I think you can try the suggestion below as well. This is doing Preceding Load, if you add another month to the Inline table now it will be included in the CrossTable.

Data:

CrossTable(Month,Sales)

LOAD *

;

LOAD * INLINE [

Region, Jan-2016, Feb-2016, Mar-2016, Apr-2016

UK, 200, 563, 897

Manchester, 33, 321, 546

Birmingham, 1005, 200, 22

Leverpool, 899, 546, 145

Florida, 800,563,546,3300

];