Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

excel pivot table

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

excel pivot table

This is the source data.

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

brenner_martina
Valued Contributor II

excel pivot table

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

excel pivot table

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
Valued Contributor II

excel pivot table

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

excel pivot table

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

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

brenner_martina
Valued Contributor II

excel pivot table

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

Not applicable

excel pivot table

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
Valued Contributor II

excel pivot table

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
Community Browser