Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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