nested concat on pivot table

[shruthibk]

Hi All,

I have a requirement to concat two fields to show in a pivot table.

Week, Category, title

1,beauty,Test1

1,beauty,Test2

1,Espresso,Test2

expected output

week  1

 test1,test2(beauty),Test2(espresso)

Kindly help me with the concat function 

Thanks,

Shruthi B K

[jaibau1993]

Hi!

Use something like the following as expression:

Concat(Distinct title & '(' & Category & ')')

Regards,

Jaime. 

[shruthibk]

the expression doesn't return the desired output

[shruthibk]

the suggested expression is giving the output

 test1(beauty),test2(beauty),Test2(espresso)

but expected output is 

test1,test2(beauty),Test2(espresso)

[Anil_Babu_Samineni]

What is the O/P you need from below table?

Week, Category, title

1,beauty,Test1

1,Espresso,Test2

1,beauty,Test1

1,Espresso1,Test2

[shruthibk]

for week 1

Test1(beauty),Test2(Espresso,Espresso1)

[sunny_talwar · Accepted solution]

You can try this

=Concat(DISTINCT Aggr(Concat(DISTINCT title, ',') & '(' & Category & ')', Week, Category), ',')

[shruthibk]

Thank you Sunny 🙂

[sunny_talwar]

But this doesn't seem to work with the solution you provided to @Anil_Babu_Samineni updated sample.

[bixpahalley]

you can try this, it's work.

=Concat(DISTINCT Aggr(Concat(DISTINCT title, ',') & '(' & Concat(DISTINCT Category,',') & ')', Week, Category), ',')