topic Merge Multiple Rows & Columns Into a single Comma Separated Row in QlikView

Merge Multiple Rows & Columns Into a single Comma Separated Row

rahulkatte007 — Fri, 15 Nov 2024 23:34:47 GMT

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

Re: Merge Multiple Rows & Columns Into a single Comma Separated Row

edwin — Mon, 23 Nov 2020 18:43:52 GMT

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

Re: Merge Multiple Rows & Columns Into a single Comma Separated Row

MayilVahanan — Tue, 24 Nov 2020 06:13:45 GMT

Hi @rahulkatte007

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:

Re: Merge Multiple Rows & Columns Into a single Comma Separated Row

rahulkatte007 — Tue, 01 Dec 2020 18:26:49 GMT

Thanks Mayil it works.