8 Replies Latest reply: Oct 17, 2012 11:08 AM by Ewelina Eggert

# How to concatenate values in one of the dimention in Pivot

I have pivot table that contain list of customers with details like company name, phone number, address etc. and it shows in crosstab columns brands used by customer. Unfortunately for one customer we can have more than one phone number and then I have a problem - I should show all numbers, but concatenated into one cell.
Now I have something like this:
Company namePhone numberAddressBrand 1Brand 2Brand 3
Company 1111 111 111 address 1xx
and I would like to achieve something like this:
Company namePhone numberAddressBrand 1Brand 2Brand 3
Company 1111 111 111 ; 111 222 111; 111 111 333address 1xx
Company 3222 222 222; 222 222 111address 3xx
On the straight table I could use concatenate function on Phone number and define it as Expression not as dimention. Unfortunately my Brand information is stored in the table like (Brand, Company) and phone numbers are in similar data structure - in both cases I have relationships one company to many (brands or phone numbers).
How I can achieve what I need?
• ###### Re: How to concatenate values in one of the dimention in Pivot

Try a calculated dimension, like

=concat(PHONENUMBER, '; ')

• ###### Re: How to concatenate values in one of the dimention in Pivot

It does not work - I get message "Error in Expression". I can use concat () function only in expression. But this does not meet my need as then I see phone number under brands (where I use count function to get x when given brand exist for given customer).

• ###### Re: How to concatenate values in one of the dimention in Pivot

Yes, sorry, my fault.

The calculated dimension should look like:

=aggr(concat([Phone number],'; ' ),[Company name])

Stefan

• ###### Re: How to concatenate values in one of the dimention in Pivot

swuehl wrote:

Yes, sorry, my fault.

The calculated dimension should look like:

=aggr(concat([Phone number],'; ' ),[Company name])

Stefan

Thank you swuehl

Maybe at some point I'll know all the tricks necessary to design in QlikView

• ###### Re: How to concatenate values in one of the dimention in Pivot

Hi,

You can do the concat() function in expression then use pivot table to move the column after company name.

Regards,

Janzen

• ###### Re: How to concatenate values in one of the dimention in Pivot

Hi,

It's recommended to create a new field containing all numbers for a company in your datamodel then use it as dimension.

Use Concat(Phone Number, ' / ') group by Company Name.

Regards

Vincent

RegarPhPhoPhone numberone number

• ###### Re: How to concatenate values in one of the dimention in Pivot

In my case it's not so easy as I can't change the datamodel myself.

But thank you for answer that is in line with my thoughts...

• ###### Re: How to concatenate values in one of the dimention in Pivot

Janzen Jino B wrote:

Hi,

You can do the concat() function in expression then use pivot table to move the column after company name.

Regards,

Janzen

I've tried, without result.