Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

How to concatenate values in one of the dimention in Pivot

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 namePhone numberAddressBrand 1Brand 2Brand 3
Company 1111 111 111 address 1xx
Company 1111 222 111address 1xx
Company 1111 111 333address 1xx
Company 2122 123 123address 2xx
Company 3222 222 222address 3xx
Company 3222 222 111address 3xx
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 2122 123 123address 2x
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?
1 Solution

Accepted Solutions
MVP
MVP

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

See also attached.

Stefan

8 Replies
MVP
MVP

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

Try a calculated dimension, like

=concat(PHONENUMBER, '; ')

Not applicable

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

MVP
MVP

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

See also attached.

Stefan

Not applicable

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

vincentmecrin
New Contributor III

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

Not applicable

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

Not applicable

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.

Not applicable

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

See also attached.

Stefan

Thank you swuehl

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

Community Browser