Merge data from same column

steasy — Fri, 12 Apr 2024 07:45:15 GMT

Hello,

I have datasets like the following:

Name	Age	Gender	Activity	Revenue
Robert	30	Male	Swimming	5 $
Robert	30	Male	Football	5 $

So I have many rows containing data like this. And the revenue is adding up together, although it is just 5 $.

The column Activities is needed for context. Therefore I want something like this:

Name	Age	Gender	Activities	Revenue
Robert	30	Male	Swimming; Football	5 $

Is there a good way to achieve this in script or are there other/better solutions?

Thanks in advance!

Re: Merge data from same column

brunobertels — Fri, 12 Apr 2024 08:34:21 GMT

Here an example script to adapt to your data :

Data:

Load * inline [
ID, Name, Property, Across, Lat, Long
1, John, A, 10, 10.4, -12.2
2, Sue, B, , 11.45, -15.67
3, Mark, C, 11, 12.1, -13.1
4, Mark, D, 11, 12.1, -13.1
5, Smith, E, 1416, 15.6, -28.2
6, Smith, F, 1416, 15.6, -28.2
7, David, G, 28, 55.2, -89.5
8, David, H, 28, 55.2, -89.5
9, David, I, 28, 55.2, -89.5
10, Rita, J, 12, 14.7, -89.99
11, Henry, K, 43, 22.78, -78.35
12, Henry, K, 43, 22.78, -78.35
13, Andrew, L, 12, 31.89, -90.56
14, Steven, M, 13, 31.89, -90.56
];

Tmp:
NoConcatenate
load ID, Name, Across, Lat, Long resident Data;
left join load Name,concat(DISTINCT Property,'/') as [Property New], Across, Lat, Long resident Data Group By Name,Across,Lat,Long;

drop table Data;

output:
NoConcatenate

load ID ,if(peek([Property New])=[Property New],0,[Property New]) as [Property New],Name, Across, Lat, Long;
load * resident Tmp /*order by ID desc*/;

drop Table Tmp;

topic Merge data from same column in App Development

Merge data from same column

Re: Merge data from same column