topic Re: transpose row to column with it's own value in script in App Development

transpose row to column with it's own value in script

Valerie — Sat, 16 Nov 2024 04:42:07 GMT

I would like to transpose data from row to column with it's own data value.

Here is the original data sample:

ID	Created at	Remarks
1001	1/5/2019	No Answer
1001	3/5/2019	Cancelled
1001	30/4/2019	Called
1002	2/5/2019	No Answer
1002	1/5/2019	Follow Up

I would like the result to be as below:

ID	Remarks 1	Remarks 2	Remarks 3
1001	Called	No Answer	Cancelled
1002	Follow Up	No Answer

Group by the ID, remarks should be order by created at date or time.

Re: transpose row to column with it's own value in script

Vegar — Thu, 12 Sep 2019 04:12:32 GMT

Firstly you need to generate a remark fiel pointing out which remark each row is.

ID	Created at	RemarkNo	Remarks
1001	1/5/2019	Remark2	No Answer
1001	3/5/2019	Remark3	Cancelled
1001	30/4/2019	Remark1	Called
1002	2/5/2019	Remark2	No Answer
1002	1/5/2019	Remark1	Follow Up

When you have this information per row as in the table above, then you can start "transposing" it using generic load followed by a join of the generic load output. Take a look at @hic blog The Generic Load

Re: transpose row to column with it's own value in script

asinha1991 — Thu, 12 Sep 2019 06:57:38 GMT

elaborating on what @Vegar already said, you can do something like this...first load data in sometable..then do this.

TmpTable:

Load *,'Remark '&autonumber(rowno(),ID) as RemarkName;

Load ID,Remarks Resident Sometable order by created at;

Drop sometable;

FinalTable;

Generic Load ID, RemarkName,Remarks Resident TmpTable;

Drop TmpTable;

Re: transpose row to column with it's own value in script

mahaveerbiraj — Thu, 12 Sep 2019 08:58:17 GMT

Hello,

please try with below logic,

temp:

load *

Inline [
ID, Created at, Remarks,
1001, 1/5/2019, No Answer
1001, 3/5/2019, Cancelled
1001, 30/4/2019, Called
1002, 2/5/2019, No Answer
1002, 1/5/2019, Follow Up
];

left join(temp)
temp2:
Load
*
Inline
[ Remarks, Remarks_final
No Answer,Remarks 2
Cancelled,Remarks 3
Called ,Remarks 1
Follow Up ,Remarks 1
];

final:
Generic
load
ID,
Remarks_final,
Remarks
Resident temp;

Drop Table temp;