Concatenating Data

Deborah Bell

I am getting requests from users for what amounts to concatenating varchar data from different rows in the same table. The best example is tracking; a sales order can ship out in multiple boxes that have different tracking numbers. So I have a table with a list of tracking numbers that corresponds to the sales order numbers, and users would prefer to see the tracking numbers concatenated into one field in one row, i.e., order 123 has "Tracking 1, Tracking 2, Tracking 3" rather than having three rows for order 123 to list each tracking number separately (which is the result when you just link the two tables).

The software we're moving away from uses a CASE loop to accomplish this, but I'm not sure what the appropriate loop in a Birst scripted source would be. I'm guessing it's either a For or While loop. Has anyone else done something similar? One problem is that there's no way to know how many tracking numbers there will be for each order number.

Dan_Rosen

Hi Deborah, if you want to pull together data from multiple rows in a script, typically you would want to do the following:

• Ensure the script is ordered by a set of keys (let's say company, sales order, sales order line) where all of the rows you want to concatenate would be in order
• Create variables in the script to track and compare the keys (minus the last) from the current record to the last record so you can see when the sales order changes
• Have a variable that you are adding/concatenating the value of each record within

If you need one record only per set of records you can control it with the WriteRecord keyword. If you need this new field on every line, you could join the output of this script with your original one.

This is relatively high level assuming you know how to do the detailed work in here, but if not I'm sure there are plenty of people here who can point you further in the right direction.