Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have struck one place kindly give me suggestion on this.
I have data like this
Header 1 | Header 2 | Header 3 | Header 4 | Header 5 | Header 6 | Header 7 |
---|---|---|---|---|---|---|
Country | Indicator | 1990 | 1991 | 2013 | 2014 | 2015 |
India | helth | 10 | 11 | 12 | 13 | 14 |
in this case i want to create one year field instead of individual years, for this i have created cross table every this is fine but instead of value year is showing under Value tab.
Please find the attached document ,
kindly give me suggestion on this.
thanks in advance..
Try this:
Table:
CrossTable(Year, Value, 2)
LOAD [Country Name],
[Indicator Name],
[1960],
[1961],
[1962],
[1963],
[1964],
[1965],
[1966],
[1967],
[1968],
[1969],
[1970],
[1971],
[1972],
[1973],
[1974],
[1975],
[1976],
[1977],
[1978],
[1979],
[1980],
[1981],
[1982],
[1983],
[1984],
[1985],
[1986],
[1987],
[1988],
[1989],
[1990],
[1991],
[1992],
[1993],
[1994],
[1995],
[1996],
[1997],
[1998],
[1999],
[2000],
[2001],
[2002],
[2003],
[2004],
[2005],
[2006],
[2007],
[2008],
[2009],
[2010],
[2011],
[2012],
[2013],
[2014],
[2015]
FROM
Crosstable2.xlsx
(ooxml, embedded labels, table is Sheet1);
FinalTable:
NoConcatenate
LOAD Year(Date#(Year, 'YYYY')) as Year,
Value,
[Country Name],
[Indicator Name]
Resident Table
Where Len(Trim(Value)) > 0;
DROP Table Table;
Try this:
Table:
CrossTable(Year, Value, 2)
LOAD [Country Name],
[Indicator Name],
[1960],
[1961],
[1962],
[1963],
[1964],
[1965],
[1966],
[1967],
[1968],
[1969],
[1970],
[1971],
[1972],
[1973],
[1974],
[1975],
[1976],
[1977],
[1978],
[1979],
[1980],
[1981],
[1982],
[1983],
[1984],
[1985],
[1986],
[1987],
[1988],
[1989],
[1990],
[1991],
[1992],
[1993],
[1994],
[1995],
[1996],
[1997],
[1998],
[1999],
[2000],
[2001],
[2002],
[2003],
[2004],
[2005],
[2006],
[2007],
[2008],
[2009],
[2010],
[2011],
[2012],
[2013],
[2014],
[2015]
FROM
Crosstable2.xlsx
(ooxml, embedded labels, table is Sheet1);
FinalTable:
NoConcatenate
LOAD Year(Date#(Year, 'YYYY')) as Year,
Value,
[Country Name],
[Indicator Name]
Resident Table
Where Len(Trim(Value)) > 0;
DROP Table Table;
Thank you very much for reply Sunny..........
it is working for me...........................