Evaluate Max Length of a Dimension and more...

[kevin_creese]

Hello Qlik Community,

   I have a business case where I have a list of customers.  These customers have multiple customer numbers across different ERP systems.  These customers have a are related to a billing customer number.  An example data set would look like this:

Table: T1 - Original Table

cus_no	parent_no	Name	Addr1	Addr2	City	State	Zip	Ship_to
180257	100657	Bob's Dogs & Burgers	I8 Beef St.	Suite 200	Burgerville	MN	55347	1122
180257	100657	Bob's Dogs & Burgers	I8 Beef St.	Suite 200	Burgerville	MN	55347	2014
180257	100657	Bob's Dogs & Burgers	I8 Beef St.	Suite 200	Burgerville	MN	55347	16573
180257	100657	Bob's Dogs & Burgers	I8 Beef St.	Suite 200	Burgerville	MN	55347	196220

What I've been asked to do is convert this data into a single line where all of the "Ship_to" are separated by a '|'. 

I was able to utilize the Peek function to build the string and now my data looks like this:

Table: T2 - After Peek !

cus_no	parent_no	Name	Addr1	Addr2	City	State	Zip	Ship_to
180257	100657	Bob's Dogs & Burgers	I8 Beef St.	Suite 200	Burgerville	MN	55347	1122
180257	100657	Bob's Dogs & Burgers	I8 Beef St.	Suite 200	Burgerville	MN	55347	2014|1122
180257	100657	Bob's Dogs & Burgers	I8 Beef St.	Suite 200	Burgerville	MN	55347	16573|2014|1122
180257	100657	Bob's Dogs & Burgers	I8 Beef St.	Suite 200	Burgerville	MN	55347	196220|16573|2014|1122

Ultimately I need to get the data to look like this:

Table: PleaseHelp

cus_no	parent_no	Name	Addr1	Addr2	City	State	Zip	Ship_to
180257	100657	Bob's Dogs & Burgers	I8 Beef St.	Suite 200	Burgerville	MN	55347	196220|16573|2014|1122

Can anyone recommend a solution for me?  I have attached a sample to review

[marcus_sommer · Accepted solution]

You could use a string-aggregation like:

load cus_no, concat(Ship_to, '|') as Ship_to_concat

from Source group by cus_no;

- Marcus

[kevin_creese]

This looks like a beautiful option!  Maybe I was making this harder than I need to.  I'll test in the full example and confirm that this will work for me.  I updated the test qvw with your recommendation and got the result I was looking for.

Thank you for the prompt reply. The Qlik Community is wonderful!