Cross-Table load from Excel files

[nagaiank]

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.

[swuehl · Accepted solution]

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 ...

[nagaiank]

Thank you Stefan. It worked.

[Gabriel]

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

];