Custom Data Issue

[#1]

A	B
1009	1
KSN	2
USA	3
2009	4
NNN	5
UK	6
5077	7
LLT	8
UK	9

  Table 1 (Input)

Above table is the sample data.I want the above table to cross table in the below format.Any help...

Thanks

ID	Name	Country
1009	KSN	USA
2009	NNN	UK
5077	LLT	UK

[erichshiino · Accepted solution]

Hi,

I segmented your original table into three and them joined them back to create column.

temp:
LOAD * INLINE [
    A, B
    109, 1
    KSN, 2
    USA, 3
    2009, 4
    NNN, 5
    UK, 6
    5077, 7
    LLT, 8
    UK, 9
];
Table1:
Load A, 
          B, 
          Floor((B-1)  /  3)  AS C //EACH GROUP OF 3 ROWS WILL HAVE THE SAME C
RESIDENT temp; 
drop table temp; 
Final:
Load A AS ID, C as joinField resident Table1 
where mod(B-1,3)  =  0 //GETS THE FIRST LINE OF EACH GROUP
;
left join(Final)
Load A as Name, C as joinField resident Table1 where  mod(B-1,3)  =  1 
 //GETS THE SECOND LINE OF EACH GROUP
 ; 
left join(Final)
Load A as Country, C as joinField resident Table1 where  mod(B-1,3)  =  2 
 //GETS THE THRID LINE OF EACH GROUP
 ; 
drop table Table1 ; 
//You may drop the Joinfield
//drop field joinField ; 

Hope it helps,

Erich

[#3]

Hi Erich,

Thanks ..It works perfect.

Regards