How to transpose a table in script?

[menphis12]

Hi ,

I am trying to tranpose a table but without sucess.

There, my file where i have an ID and few user per ID

I need to tranpose these table and get that :

If i can do it automatically, it will be better.
I need to calculate a max of user per ID (of example 3 in our case ), and generate three Colomn.

Many thanks,

Avy

[Taoufiq_Zarra]

One Solution :

Input:

load ID,if(previous(ID)=ID,'User_'&(peek('ValuTmp')+1),'User_1') as UserID,if(previous(ID)=ID,peek('ValuTmp')+1,1) as ValuTmp, User;
load * inline [
ID,User
1,AB
1,BC
2,CD
2,EF
2,GH
3,IJ

];

drop fields ValuTmp;



CombinedGenericTable:

Load distinct ID resident Input;

DATA:
Generic load * resident Input;

drop table Input;
   
   

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'DATA.*') THEN 

  LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF 

NEXT i

output :

[Saravanan_Desingh]

One solution is.

tab1:
Generic
LOAD ID, 'User_'&AutoNumber(RowNo(),ID), User;
LOAD * INLINE [
ID,User
1,AB
1,BC
2,CD
2,EF
2,GH
3,IJ
];

[Saravanan_Desingh]

Output.