Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm having a problem with a data set that I need to combine in different ways in QV.
The problem is the data sources, I do not understand how I can make charts/pivots of this information.
The data is year-based and looks like this:
Country | 2001 | 2002 | 2003 | 2004 |
---|---|---|---|---|
USA | 30 | 55 | 53 | 43 |
Canada | 20 | 67 | 235 | 12 |
England | 10 | 45 | 45 | 24 |
France | 50 | 58 | 45 | 34 |
Germany | 40 | 76 | 35 | 74 |
How do I turn this "back" into something like this (or do I even need to?):
Country | Year | Amount |
---|---|---|
USA | 2001 | 30 |
USA | 2002 | 55 |
USA | 2003 | 53 |
USA | 2004 | 43 |
Canada | 2001 | 20 |
Canada | 2002 | 67 |
Canada | 2003 | 235 |
Canada | 2004 | 12 |
Hi,
See the attached sample file try to load data some thing like this.
By below code
Data:
CrossTable(Year, Amount)
LOAD Country,
[2001],
[2002],
[2003],
[2004]
FROM
Data.xlsx
(ooxml, embedded labels, table is Sheet1);
Let me know about this
HTH
Rgds
Anand
Hi Gabriel,
try using CrossTable...I tried with this snippet....
YrsData:
Load * Inline [
country, 2001, 2002, 2003
A, 1, 2, 3
B, 2,3,4
C,3,4,5
];
CrossTable(Years, Sales) Load * Resident YrsData;
Hi,
See the attached sample file try to load data some thing like this.
By below code
Data:
CrossTable(Year, Amount)
LOAD Country,
[2001],
[2002],
[2003],
[2004]
FROM
Data.xlsx
(ooxml, embedded labels, table is Sheet1);
Let me know about this
HTH
Rgds
Anand
Thanks a million for pointing me in the right direction, I still dont understand how it works but seems QV has a CroosTable -wizzard built in that does the magic for you