merging countrys into one cell for one id

[Sra1bandi]

Hi Team,

Inline

[ Id    Country 

1       India 

1       Australia

1       Us

2       Uk

2       Nz

3       china

3       dubai

3       nepal ]

Result i need:

Id                 Country 

1                   India , Australia,Us

2                   Uk, Nz

3                    china, dubai,nepal

Thanks & Regards,

Sra1

[Vegar]

Try using the Concat() function.

LOAD Id, Concat(Country, ', ') as Countries

Inline

[ Id Country 

1 India 

1 Australia

1 Us

2 Uk

2 Nz

3 china

3 dubai

3 nepal ]

Group By Id;

[ajaykakkar93]

hi,


data:
load Id,Concat(Country,',') as Country Group by Id;
load Id,Capitalize(trim(Country)) as Country;
load * Inline
[Id,Country
1,India
1,Australia
1,Us
2,Uk
2,Nz
3,china
3,dubai
3,nepal ];

[SunilChauhan]

Temp:

load * Inline
[Id,Country
1,India
1,Australia
1,Us
2,Uk
2,Nz
3,China
3,Dubai
3,Nepal ];

load Id,Concat(Country,',') as Country,
1 as junk
resident Temp Group by Id;
Drop Table Temp;

See the attached file too

[Sra1bandi]

if id and countrys are in different tables how to do that?

[SunilChauhan]

there should be SOme relation Between Tables 

like id should Link to ID in Second table 

other wise Cross join occur 

and one id  will join with All the Countries

Example

Table1:

ID

1

Table2:

Countries

India

US

UK

if join Then 

we have below Result

1 India

1 US

1 UK

and after merging

1,India US UK

[Vegar · Accepted solution]

Are you required to make the aggregation in the script?

What about keeping your data as it is, and instead do the concat in the visualisation in the user application ?

Create a straight table with [Id] as a dimension and Concat(distinct [Country], ', ') as a measure?