How to concatenate values in one of the dimention in Pivot

[#1]

Hello, could you please help me to solve the problem:

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 name	Phone number	Address	Brand 1	Brand 2	Brand 3
Company 1	111 111 111	address 1	x		x
Company 1	111 222 111	address 1	x		x
Company 1	111 111 333	address 1	x		x
Company 2	122 123 123	address 2	x	x
Company 3	222 222 222	address 3		x	x
Company 3	222 222 111	address 3		x	x

and I would like to achieve something like this:

Company name	Phone number	Address	Brand 1	Brand 2	Brand 3
Company 1	111 111 111 ; 111 222 111; 111 111 333	address 1	x		x
Company 2	122 123 123	address 2		x
Company 3	222 222 222; 222 222 111	address 3		x	x

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?

[swuehl]

Try a calculated dimension, like

=concat(PHONENUMBER, '; ')

[#3]

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).

[swuehl · Accepted solution]

Yes, sorry, my fault.

The calculated dimension should look like:

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

See also attached.

Stefan

[#5]

Hi,

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

Regards,

Janzen

[Anonymous]

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

[#7]

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...

[#8]

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.

[#9]

swuehl wrote:

Yes, sorry, my fault.

The calculated dimension should look like:

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

See also attached.

Stefan

Thank you swuehl

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

[Meghann_MacDonald]

Hi there - This worked for my app as well, and returned 

Where my multiple URLS are returned for one Title. However, once I select representation as URL instead of text, the cell returns completely blank. Is there a workaround to have them appear as clickable links separated by ; ?