Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
hi
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;
hi
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;