Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You could use a string-aggregation like:
load cus_no, concat(Ship_to, '|') as Ship_to_concat
from Source group by cus_no;
- Marcus
You could use a string-aggregation like:
load cus_no, concat(Ship_to, '|') as Ship_to_concat
from Source group by cus_no;
- Marcus
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!