Making Field Names one field

[#1]

Hi, i have a excel sheet which i want to load into qlikview to create some charts and pivot tables.

One problem i have is that i have each Month as a field its self with data in. This is fine for the pivot tables, but when i want to create a graph i want the date fields to be the axis but they are seperate fields.

Here is what the excel spreadsheet looks like:

Resource	Project	Segment	% Deployed Target	Project Start	Project End	Sept 12	Oct 12	Nov 12	Dec 12	Jan 13	Feb 13	Mar 13	Apr 13	May 13	Jun 13	Jul 13	Aug 13	Sep 13
1	A	F	20	01/03/2012	31/03/2013	20	20	20	20	20	20	20	20	20	20	20	20	20
1	G	F	30	01/08/2012	30/09/2012
1	L	F	20	01/03/2012	30/06/2012
1	Z	F	20	23/03/2012	20/07/2012
1	Y	F	30	14/07/2012	10/12/2012
1	X	F	20	23/03/2012	05/06/2012
1	T	F	10	01/06/2012	31/08/2012
1	N	F	30	01/10/2012	31/10/2012
1	O	F	40	14/07/2012	30/09/2012
1	M	F	50	01/05/2012	31/05/2012	37
1	Q	F	20	01/04/2012	30/10/2012	33	33	20
1	P	F	50	15/08/2012	30/11/2012
1	S	F	30	01/06/2012	30/06/2012
1	V	F	13	01/06/2012	31/10/2012		13
1	D	F	10	01/06/2012	30/09/2012	10
1	K	F	70	20/09/2012	31/03/2013		30	60	35	80	80	80
1	C	N/A	N/A	N/A	N/A				45
2	A	F	20	01/03/2012	31/03/2013	20	20	20	20	20	20	20
2	E	F	40	01/03/2012	31/05/2012	0	0	0	0	0	0	0
2	H	F	10	01/04/2012	30/10/2012	30	10	0	0	0	0	0
2	Z	F	75	23/03/2012	20/07/2012	10	10	10	10	10	10	10
2	Y	F	35	14/07/2012	10/12/2012	10	30	40	20	0	0	0
2	X	F	20	23/03/2012	05/06/2012	0	0	0	0	0	0	0
2	P	F	50	15/08/2012	30/11/2012	30	30	30	50	50	50	50
2	AB	F	30	01/07/2012	31/07/2012	0	0	0	0	20	20	0
3	A	F	20	01/03/2012	31/03/2013	20	20	20	20	20	20	20
3	I	F	40	01/09/2012	28/02/2013
3	J	F	40	01/04/2012	30/09/2012	20
3	L	F	20	01/03/2012	30/06/2012
3	Q	F	20	01/04/2012	30/10/2012	30	20
3	K	F	12	20/09/2012	31/03/2012	30	60
4	B	E	30	30/03/2012	30/10/2012	10	10	10	10	10	10	10
4	F	F	10	01/03/2012	31/03/2013	5	5	5	5	5	5	5
4	Z	E	30	23/03/2012	20/07/2012	10	10	10	10	10	10	10
4	Y	E	30	14/07/2012	10/12/2012	30	30	30	30	30	30	30
4	X	E	30	23/03/2012	05/06/2012	0	0	0	0	0	0	0
4	U	E	30	05/05/2012	14/07/2012	5	5	5	5	5	5	5

With the data like this i can easily create pivot tables with resource capicity by month as shown below:

Resource	Oct 12	Nov 12	Dec 12	Jan 13	Feb 13	Mar 13	Apr 13	May 13	Jun 13	Jul 13	Aug 13	Sep 13
1	96	100	100	100	100	100	20	20	20	20	20	20
2	100	100	100	100	100	80	0	0	0	0	0	0
3	100	20	20	20	20	20	0	0	0	0	0	0
4	80	70	70	70	70	70	0	0	0	0	0	0
5	85	100	100	95	95	95	0	0	0	0	0	0

however if i wanted to create a graph or be able to select a month how would i  be able to do this as they are current individual fields?

Thanks

Laura

[stevedark · Accepted solution]

Hi Laura,

This is a relatively simple CROSSTABLE, if you search on that statement you should find plenty of examples.  The only thing that may take a little more effort is getting the date to behave correctly.  I would probably do the crosstable into a temporary table and then manipulate the date in a RESIDENT load.

Hope that points you in the correct direction.

- Steve

http://www.quickintelligence.co.uk/