Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

excel pivot table

Hi,

Can someone help me with the following excel structure?

I need to read this into qlikview.

HistoricLU
CountryProductQ1/05Q2/05Q3/05Q1/05Q2/05
BelgiumXeplion123234345456567
GermanyXeplion123234345456567
BelgiumStelare234345456567678
10 Replies
Not applicable
Author

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.

Not applicable
Author

This is the source data.

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

brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi Elke

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

Historic
CountryProductQ1/05Q2/05Q3/05
BelgiumXeplion123234345
GermanyXeplion123234345
BelgiumStelare234345456

LU
CountryProductQ1/05Q2/05
BelgiumXeplion456567
GermanyXeplion456567
BelgiumStelare567678


Greetings from munich

Martina

EVACO GmbH

Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

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

Not applicable
Author

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

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

brenner_martina
Partner - Specialist II
Partner - Specialist II

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

Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

Best way is like this

CountryProductFieldQuarterValue
BelgiumXeplionHistoricQ1/05123
GermanyXeplionHistoricQ1/05123
BelgiumStelareHistoricQ1/05234
BelgiumXeplionHistoricQ2/05234
GermanyXeplionHistoricQ2/05234
BelgiumStelareHistoricQ2/05345
BelgiumXeplionHistoricQ3/05345
GermanyXeplionHistoricQ3/05345
BelgiumStelareHistoricQ3/05456
BelgiumXeplionLUQ1/05456
GermanyXeplionLUQ1/05456
BelgiumStelareLUQ1/05567
BelgiumXeplionLUQ2/05567
GermanyXeplionLUQ2/05567
BelgiumStelareLUQ2/05678