Cross table issue

[ravindraa]

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..

[sunny_talwar · Accepted solution]

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;

[ravindraa]

Thank you very much for reply Sunny..........

it is working for me...........................