Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to Concatenate the Multiple rows ,Fields value into Single comma separated row.
The below Source table having ID,User,Comments & Comments_Update_Timestamp fields.
The source Table
The Expected output should be displayed mentioned below table i.e. User,Comments & Comments_Update_Timestamp
fields should be merged & displayed into single row Group by Task ID.
Attached application with sample Data .
Thanks
Try like below
T:
LOAD *, Date(Date#(Date, 'DD/MM/YYYY hh:mm')) as Date1 Inline
[
Task_ID, User ,Comments ,Date
5110846, John ,Reassigned, 19/12/2020 12:32
5110846, SAM ,Resent, 20/12/2020 05:32
5110846, RAM ,Closed, 21/12/2020 16:32
];
Load Task_ID, Concat(User & ' ('&Date&') '&' - '&Comments, chr(10), Date1) as Combine Resident T
group by Task_ID;
DROP Table T;
Result:
try this:
data:
load * inline [
Task, User, Comment, Date
5100, John, Assigned, 1/1/2020
5100, Jay, Closed, 1/2/2020
5100, Joe, Resent, 1/3/2020
5200, Joh, Assigned2, 2/1/2020
5200, Ja, Closed2, 2/2/2020
5200, Jo, Resent2, 2/3/2020
];
NoConcatenate
load Task, concat(User & ' ' & Comment & ' ' & Date, '|') as Field Resident data group by Task;
drop table data;
this is the result:
Task | Field |
5100 | Jay Closed 1/2/2020|Joe Resent 1/3/2020|John Assigned 1/1/2020 |
5200 | Ja Closed2 2/2/2020|Jo Resent2 2/3/2020|Joh Assigned2 2/1/2020 |
Try like below
T:
LOAD *, Date(Date#(Date, 'DD/MM/YYYY hh:mm')) as Date1 Inline
[
Task_ID, User ,Comments ,Date
5110846, John ,Reassigned, 19/12/2020 12:32
5110846, SAM ,Resent, 20/12/2020 05:32
5110846, RAM ,Closed, 21/12/2020 16:32
];
Load Task_ID, Concat(User & ' ('&Date&') '&' - '&Comments, chr(10), Date1) as Combine Resident T
group by Task_ID;
DROP Table T;
Result:
Thanks Mayil it works.