excel pivot table

[#1]

Hi,

Can someone help me with the following excel structure?

I need to read this into qlikview.

		Historic			LU
Country	Product	Q1/05	Q2/05	Q3/05	Q1/05	Q2/05
Belgium	Xeplion	123	234	345	456	567
Germany	Xeplion	123	234	345	456	567
Belgium	Stelare	234	345	456	567	678

[#2]

If you double click on the total value of the pivot table in excel, you 'll get a sheet with the raw data. Import the data into qlikview.

[#3]

This is the source data.

It has the structure from a pivot table but isn't one in excel.

[brenner_martina]

Hi Elke

you have to load a crosstable, load at first the Historic data, than the LU-Data.

		Historic
Country	Product	Q1/05	Q2/05	Q3/05
Belgium	Xeplion	123	234	345
Germany	Xeplion	123	234	345
Belgium	Stelare	234	345	456

					LU
Country	Product				Q1/05	Q2/05
Belgium	Xeplion				456	567
Germany	Xeplion				456	567
Belgium	Stelare				567	678

Greetings from munich

Martina

EVACO GmbH

[#5]

Martina,

This is not an option because we can't work with named ranges in excel and the data will extend every year.

[brenner_martina]

Hi, Elke,

youtr script would look like this:

DataTmp:
LOAD Country,
     Product,
     'Historic' As Field,
     [Q1/05],
     [Q2/05],
     [Q3/05]
FROM
Crosstable.xls
(biff, embedded labels, header is 1 lines, table is Tabelle1$);

Concatenate (DataTmp)
LOAD Country,
     Product,
     'LU' As Field,
     [Q1/051] As [Q1/05],
     [Q2/051] As [Q2/05]
FROM
Crosstable.xls
(biff, embedded labels, header is 1 lines, table is Tabelle1$);

Data:
CrossTable(Quarter, Value, 3)
LOAD Country,
     Product,
     Field,
     [Q1/05],
     [Q2/05],
     [Q3/05]
Resident DataTmp;

Drop Table DataTmp;

Greetings from Munich

Martina

EVACO GmbH

[#7]

Ok but what if next year a new quarter is added?

Than I need to change the whole script again...

[brenner_martina]

OK, Elke, if you have to work with such awfull sources ...

[#9]

This isn't an awfull source. This is a normal source used by a business owner to fill in data.

How would you suggest the business owner fills in the data on a user friendly way?

[brenner_martina]

Best way is like this

Country	Product	Field	Quarter	Value
Belgium	Xeplion	Historic	Q1/05	123
Germany	Xeplion	Historic	Q1/05	123
Belgium	Stelare	Historic	Q1/05	234
Belgium	Xeplion	Historic	Q2/05	234
Germany	Xeplion	Historic	Q2/05	234
Belgium	Stelare	Historic	Q2/05	345
Belgium	Xeplion	Historic	Q3/05	345
Germany	Xeplion	Historic	Q3/05	345
Belgium	Stelare	Historic	Q3/05	456
Belgium	Xeplion	LU	Q1/05	456
Germany	Xeplion	LU	Q1/05	456
Belgium	Stelare	LU	Q1/05	567
Belgium	Xeplion	LU	Q2/05	567
Germany	Xeplion	LU	Q2/05	567
Belgium	Stelare	LU	Q2/05	678