Merging 2 columns into 1 column and grouping

[narender123]

Hi All,

I am creating a table and I have a requirement shown in Below image:

I want to merge Column1 and Column 2 and group by Column 1 as shown result in right side in above image.

Please tell me to achieve the result.

Thanks in advance.

Narender

[Anil_Babu_Samineni]

Yes, Here you go

TeamData:

LOAD * inline [

SalesGroup|Team|Date|Amount

East|Gamma|01/05/2013|20000

East|Gamma|02/05/2013|20000

West|Zeta|01/06/2013|19000

East|Alpha|01/07/2013|25000

East|Delta|01/08/2013|14000

West|Epsilon|01/09/2013|17000

West|Eta|01/10/2013|14000

East|Beta|01/11/2013|20000

West|Theta|01/12/2013|23000

] (delimiter is '|');

Concat1:

LOAD SalesGroup,Concat(Team,',') as TeamConcat1 Resident TeamData Group By SalesGroup;

LOAD SalesGroup & '(' & TeamConcat1 & ')' as Final Resident Concat1;

[kamal_sanguri]

Try this:

[kamal_sanguri]

more simpler than previous sample:

[kamal_sanguri]

OR

Exactly same as described by you.

[antoniotiman · Accepted solution]

Hi,

try this

Dimension :

Dim1&'('&Aggr(Concat(Dim2,';'),Dim1)&')'

Expression :

Sum(Value)

Regards,

Antonio

[narender123]

hI aNIL,

I tried as you suggested but I am getting the repetition of 2nd dimension with 1st dimension.

Column2 showing like A11,A11,A11,A11,A11,A11---------------- against Agriculture.

What i did is like:

Table1:

Load the whole table 1st.

Table2:

Column 1, concat(column2,',') as COLUMN2,

sum(amount) as AMOUNT,

resident Table1

group by Column1;

Please suggest, What I did wrong?

Thanks,

[Anil_Babu_Samineni]

May be use DISTINCT Keyword

concat(DISTINCT column2,',')

[Kushal_Chawda]

Create straight table:

Dimension:

Column1:

Expression:

1) Column1-Column2

Column1 & '(' & concat( distinct Column2,',') &')'

2) Amount

Sum(Amount)

[narender123]

Hello Anil,

I am facing some problem during setting the concatenation in existing script and not getting the exact result. So I go with the antoniotiman suggestion, as per his suggestion we don't need any change in the script.

But thanks for your suggestion.

Thanks,

Narender