topic excel pivot table in QlikView

excel pivot table

Fri, 16 Dec 2011 08:59:24 GMT

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

excel pivot table

Fri, 16 Dec 2011 09:05:05 GMT

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.

excel pivot table

Fri, 16 Dec 2011 09:10:58 GMT

This is the source data.

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

excel pivot table

brenner_martina — Fri, 16 Dec 2011 09:18:35 GMT

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

excel pivot table

Fri, 16 Dec 2011 09:25:04 GMT

Martina,

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

excel pivot table

brenner_martina — Fri, 16 Dec 2011 09:38:03 GMT

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

excel pivot table

Fri, 16 Dec 2011 09:41:46 GMT

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

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

excel pivot table

brenner_martina — Fri, 16 Dec 2011 09:48:47 GMT

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

excel pivot table

Fri, 16 Dec 2011 09:58:45 GMT

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?

excel pivot table

brenner_martina — Fri, 16 Dec 2011 10:17:52 GMT

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

excel pivot table

Fri, 16 Dec 2011 10:29:59 GMT

This is the easiest way to read it in qlikview but not for the user who provides the data.