How to import such simple cross tables with shared fields?

[andreas_koehler]

Hi all,

this morning I wanted to import some simple forecast data: annual volumes and average prices of some products and some clients.

Turned out that I ended with a lot of attempts to manipulate tables, creating synthetic keys and still getting wrong results. Horrible.

I created a small example to illustrate the problem:

There are 3 clients and 2 products. Each combination has got a forecast of sales volume and price in 2 different crosstables.

I need to import this data with the fields Company, Product, Volume, Price and Year to match against real annual sales volume and price.

The sample data is inline. I also added an xlsx with same data. (I created the data.xlsx as I was wondering if there is an issue with applying crosstable to inline-data.)

The "Year" that is created using the crosstable and shared between both tables creates also a synthetic key. Interestingly I can not integrate the "Year" into my complex keys (even when I preload the data form the crosstable),

Any help is very much appreciated.

Andreas

[andreas_koehler]

Rehi!

There is a small error in the statement that does not change the situation.

PriceForecast:

CrossTable(Year, Price, 2)

LOAD Company & '|' & Product as %Key,

     [2016],

     [2017],

     [2018]

/*FROM

[data.xlsx]

(ooxml, embedded labels, table is Price);

*/

resident ImportPrice;

[sunny_talwar · Accepted solution]

How about this?

VolumeForecast:

CrossTable(Year, Volume, 2)

LOAD Company,

    Product,

    [2016],

    [2017],

    [2018]

Resident ImportVolume;

PriceForecast:

CrossTable(Year, Price)

LOAD Company,

    Product,

    [2016],

    [2017],

    [2018]

Resident ImportVolume;

FinalTable:

NoConcatenate

LOAD *

Resident VolumeForecast;

Left Join (FinalTable)

LOAD *

Resident PriceForecast;

DROP Tables ImportVolume,ImportPrice, PriceForecast, VolumeForecast;