topic Re: How to transpose a table in script? in App Development

How to transpose a table in script?

menphis12 — Sat, 16 Nov 2024 18:26:29 GMT

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

Re: How to transpose a table in script?

Taoufiq_Zarra — Thu, 25 Jun 2020 16:31:22 GMT

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 :

Re: How to transpose a table in script?

Saravanan_Desingh — Fri, 26 Jun 2020 02:54:28 GMT

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 ];

Re: How to transpose a table in script?

Saravanan_Desingh — Fri, 26 Jun 2020 02:55:29 GMT

Output.