Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
How to get reverse of subfield results ? I mean how to merge two rows into one with desired output ..
Input | |||
Action_ID | Vendor | Personnel | Date |
12221 | Ranbaxy | Anuj K | 3/3/2018 |
12221 | Ranbaxy | Anusha | 3/3/2018 |
12222 | Ranbaxy | Manish | 3/3/2018 |
12223 | Vcorp | Madhav | 4/3/2018 |
12225 | Mego | Kusum | 5/3/2018 |
12223 | Vcorp | Anirudh | 4/3/2018 |
Output | |||
---|---|---|---|
Action_ID | Vendor | Personnel | Date |
12221 | Ranbaxy | Anuj K , Anusha | 3/3/2018 |
12222 | Ranbaxy | Manish | 3/3/2018 |
12223 | Vcorp | Madhav , Anirudh | 4/3/2018 |
12225 | Mego | Kusum | 5/3/2018 |
Thanks & Regards
Shekar
Use Concat
Concat(Personnel,',')
Group By Action_ID
in script
Or in a straight table, use the expression Concat(Personnel,',') as Personnel
Use Concat
Concat(Personnel,',')
Group By Action_ID
in script
Or in a straight table, use the expression Concat(Personnel,',') as Personnel
I have tried that many time already , i dont know why its not working
A:
LOAD Action_ID,
Vendor,
Personnel,
Date
FROM
[Merge two rows into one.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Remove(Row, Pos(Top, 1)),
Remove(Col, Pos(Top, 10)),
Remove(Col, Pos(Top, 9)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 7)),
Remove(Col, Pos(Top, 6)),
Remove(Col, Pos(Top, 5))
));
B:
LOAD
Action_ID,
Vendor,
Concat(Personnel) as P,
Date(Date,'DD/MM/YYYY') AS D
Resident A Group By Action_ID,Vendor,Date ;
DROP Table B ;
My bad :
A:
LOAD Action_ID,
Vendor,
Personnel,
Date
FROM
[Merge two rows into one.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Remove(Row, Pos(Top, 1)),
Remove(Col, Pos(Top, 10)),
Remove(Col, Pos(Top, 9)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 7)),
Remove(Col, Pos(Top, 6)),
Remove(Col, Pos(Top, 5))
));
B:
LOAD
Action_ID,
Vendor,
Concat(Personnel) as P,
Date(Date,'DD/MM/YYYY') AS D
Resident A Group By Action_ID,Vendor,Date ;
DROP Table B ;
DROP TABLE A ;