topic How to import such simple cross tables with shared fields? in QlikView

How to import such simple cross tables with shared fields?

andreas_koehler — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to import such simple cross tables with shared fields?

andreas_koehler — Mon, 29 Aug 2016 17:24:04 GMT

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;

Re: How to import such simple cross tables with shared fields?

sunny_talwar — Mon, 29 Aug 2016 17:45:55 GMT

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;