topic Re: Need help to build up Excel-Database in QlikView

Need help to build up Excel-Database

Mon, 13 Aug 2012 11:07:48 GMT

Hello,

following problem:

I've created a QlikView-file that should represent costs (actual, forecast, target) and quantities (same here).

There are x productgroups and every group have a different amount of products (example: Productgroup 1 have 10 products, productgroup 2 have 7 prodcuts, ...). In one productgroup couldn't be the same product then in another.

Till now it's no problem and I will build up the table like this for example:

Productgroup	Product	Actual costs	Target costs	Forcecast costs	Actual quantitie	Target quantitie	Forecast quantitie
1. Productgroup	Product 1.1	10	20	18	35	40	37
1. Productgroup	Product 1.2	15	20	22	10	30	15
1. Productgroup	Product 1.3	12	20	15	20	30	35
2. Productgroup	Product 2.1	30	40	35	100	200	140
2. Productgroup	Product 2.2	20	30	30	5000	5000	6000
2. Productgroup	Product 2.3	12	30	30	200	400	400
2. Productgroup	Product 2.4	32	40	40	3000	5000	5000
2. Productgroup	Product 2.5	7	20	13	10	20	15

But I also need to consider the time-dimension - That means, the given table is valid for the actual year 2012. Now I need to break it down on a monthly view and I need to add the data from the last year 2011 (there the forecast get droped).

How could I add the time-dimension in this table? Should I create a complete new table? Is it possible to build it up in only one table? -> Later at Qlikview I will change the view between the years and the months (to get a more detailed view).

Thanks for every help.

Re: Need help to build up Excel-Database

Mon, 13 Aug 2012 11:59:41 GMT

you can change chart to pivot table, add time dimension and move it to the top (i mean make it horizontal view)

Re: Need help to build up Excel-Database

Mon, 13 Aug 2012 14:23:14 GMT

You mean in Excel or in QlikView change to a pivot-table?

In Excel I've now done something like this (only the first two products because is a lot of work):

Productgroup	Product	Actual costs	Target costs	Forcecast costs	Actual quantities	Target quantities	Forecast quantities	Year	Month
1. Productgroup	Product 1.1	1,00	1,00	1,00	5,00	5,00	5,00	2012	2012_01
1. Productgroup	Product 1.1	1,50	1,00	1,25	6,00	5,00	6,00	2012	2012_02
1. Productgroup	Product 1.1	0,50	2,00	0,75	3,00	10,00	2,00	2012	2012_03
1. Productgroup	Product 1.1	2,25	2,00	2,50	7,00	8,00	6,00	2012	2012_04
1. Productgroup	Product 1.1	1,25	1,00	1,00	5,00	5,00	6,00	2012	2012_05
1. Productgroup	Product 1.1	1,75	2,00	1,75	7,00	7,00	7,00	2012	2012_06
1. Productgroup	Product 1.1	0,50	1,00	0,50	4,00	5,00	5,00	2012	2012_07
1. Productgroup	Product 1.1	1,25	1,00	1,50	5,00	5,00	5,00	2012	2012_08
1. Productgroup	Product 1.1		2,00	2,00		10,00	9,00	2012	2012_09
1. Productgroup	Product 1.1		2,00			10,00		2012	2012_10
1. Productgroup	Product 1.1		1,00			5,00		2012	2012_11
1. Productgroup	Product 1.1		1,00			5,00		2012	2012_12
1. Productgroup	Product 1.2	1,00	1,00	1,00	6,00	5,00	5,00	2012	2012_01
1. Productgroup	Product 1.2	1,00	1,00	1,00	5,00	5,00	6,00	2012	2012_02
1. Productgroup	Product 1.2	1,00	2,00	1,00	6,00	8,00	7,00	2012	2012_03
1. Productgroup	Product 1.2	2,00	2,00	1,50	6,00	8,00	6,00	2012	2012_04
1. Productgroup	Product 1.2	1,00	1,00	1,00	7,00	5,00	6,00	2012	2012_05
1. Productgroup	Product 1.2	2,00	2,00	1,50	5,00	7,00	7,00	2012	2012_06
1. Productgroup	Product 1.2	1,00	1,00	1,00	5,00	5,00	5,00	2012	2012_07
1. Productgroup	Product 1.2	1,00	2,00	1,50	7,00	8,00	6,00	2012	2012_08
1. Productgroup	Product 1.2		1,00	1,50		5,00	9,00	2012	2012_09
1. Productgroup	Product 1.2		1,00			5,00		2012	2012_10
1. Productgroup	Product 1.2		2,00			9,00		2012	2012_11
1. Productgroup	Product 1.2		2,00			9,00		2012	2012_12
1. Productgroup	Product 1.1	1,00	1,00		5,00	5,00		2011	2011_01
1. Productgroup	Product 1.1	1,00	1,00		5,00	5,00		2011	2011_02
1. Productgroup	Product 1.1	1,00	2,00		5,00	10,00		2011	2011_03
1. Productgroup	Product 1.1	1,50	2,00		6,00	8,00		2011	2011_04
1. Productgroup	Product 1.1	1,50	1,00		5,00	5,00		2011	2011_05
1. Productgroup	Product 1.1	1,50	2,00		6,00	7,00		2011	2011_06
1. Productgroup	Product 1.1	2,00	1,00		6,00	5,00		2011	2011_07
1. Productgroup	Product 1.1	2,00	1,00		6,00	5,00		2011	2011_08
1. Productgroup	Product 1.1	1,00	2,00		5,00	10,00		2011	2011_09
1. Productgroup	Product 1.1	1,00	2,00		5,00	10,00		2011	2011_10
1. Productgroup	Product 1.1	2,00	1,00		5,00	5,00		2011	2011_11
1. Productgroup	Product 1.1	1,00	1,00		5,00	5,00		2011	2011_12
1. Productgroup	Product 1.2	1,00	1,00		5,00	5,00		2011	2011_01
1. Productgroup	Product 1.2	2,00	1,00		4,00	5,00		2011	2011_02
1. Productgroup	Product 1.2	2,00	2,00		5,00	8,00		2011	2011_03
1. Productgroup	Product 1.2	1,00	2,00		5,00	8,00		2011	2011_04
1. Productgroup	Product 1.2	1,00	1,00		6,00	5,00		2011	2011_05
1. Productgroup	Product 1.2	2,00	2,00		7,00	7,00		2011	2011_06
1. Productgroup	Product 1.2	1,50	1,00		6,00	5,00		2011	2011_07
1. Productgroup	Product 1.2	1,50	2,00		7,00	8,00		2011	2011_08
1. Productgroup	Product 1.2	2,00	1,00		6,00	5,00		2011	2011_09
1. Productgroup	Product 1.2	1,50	1,00		5,00	5,00		2011	2011_10
1. Productgroup	Product 1.2	1,00	2,00		4,00	9,00		2011	2011_11
1. Productgroup	Product 1.2	2,00	2,00		8,00	9,00		2011	2011_12

And this type of list I've to convert into a pivot-table?

Re: Need help to build up Excel-Database

Mon, 13 Aug 2012 15:29:04 GMT

Forget about the pivot table. Just add a column to your source to indicate the time period. You actual did this above with YEAR_MO.

YEAR_MO is now a selectable dimension.

You can stack as many years/months as you want.

If you have a file per month and the file name contains the YEAR_MO you can extrapolate that field from the filename in the load script.