Cross Table

Anonymous — Fri, 29 Jul 2016 06:37:23 GMT

Hi All,

I have a table with following column

ID, Country, Score1, Score2, Score3, Comment1, Comment2, Comment3

101,INDIA, 4,5,4,qqqqqqqq,aaaaaaaaaaaa,zzzzzzzzzz

102,USA,3,2,5,wwww,sssss,ccccc

soon.

Now i want output to be as shown below

ID, Country,Score, Comments

101,INDIA,4,qqqqqqqq

101,INDIA,5,aaaaaaaaaaaa

101,INDIA,4,zzzzzzzzzz

102,USA,3,wwww

102,USA,2,sssss

102,USA,5,ccccc

i tried Cross table but no luck..

Re: Cross Table

sunny_talwar — Fri, 29 Jul 2016 07:07:17 GMT

Try this:

Table:

CrossTable(Field, Value, 2)

LOAD * Inline [

ID, Country, Score1, Score2, Score3, Comment1, Comment2, Comment3

101,INDIA, 4,5,4,qqqqqqqq,aaaaaaaaaaaa,zzzzzzzzzz

102,USA,3,2,5,wwww,sssss,ccccc

];

FinalTable:

NoConcatenate

LOAD RowNo() as Key,

ID,

Country,

Value as Score

Resident Table

Where WildMatch(Field, 'Score*');

Join (FinalTable)

LOAD RowNo() as Key,

ID,

Country,

Value as Comment

Resident Table

Where WildMatch(Field, 'Comment*');

DROP Table Table;