Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
kevin_creese
Contributor II
Contributor II

Evaluate Max Length of a Dimension and more...

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_noparent_noNameAddr1Addr2CityStateZipShip_to
180257100657Bob's Dogs & BurgersI8 Beef St.Suite 200BurgervilleMN553471122
180257100657Bob's Dogs & BurgersI8 Beef St.Suite 200BurgervilleMN553472014
180257100657Bob's Dogs & BurgersI8 Beef St.Suite 200BurgervilleMN5534716573
180257100657Bob's Dogs & BurgersI8 Beef St.Suite 200BurgervilleMN55347196220

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_noparent_noNameAddr1Addr2CityStateZipShip_to
180257100657Bob's Dogs & BurgersI8 Beef St.Suite 200BurgervilleMN553471122
180257100657Bob's Dogs & BurgersI8 Beef St.Suite 200BurgervilleMN553472014|1122
180257100657Bob's Dogs & BurgersI8 Beef St.Suite 200BurgervilleMN5534716573|2014|1122
180257100657Bob's Dogs & BurgersI8 Beef St.Suite 200BurgervilleMN55347196220|16573|2014|1122

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

Table: PleaseHelp

cus_noparent_noNameAddr1Addr2CityStateZipShip_to
180257100657Bob's Dogs & BurgersI8 Beef St.Suite 200BurgervilleMN55347196220|16573|2014|1122

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

1 Solution

Accepted Solutions
marcus_sommer

You could use a string-aggregation like:

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

from Source group by cus_no;

- Marcus

View solution in original post

2 Replies
marcus_sommer

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
Contributor II
Contributor II
Author

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!