One to Many Join, then show all "matches" in one field

[#1]

Hello,

I have a large dataset with a few joins and mappings that has sales order information, customer info, etc. Each order is and must be on one line. I am trying to join shipment numbers to this dataset.

Normally the join is one to one, meaning for each order there is one shipment and hence once shipment number. However, sometimes with the shipments need to be split, there will be two shipments.

Is there a way to either create another field, ie Shipment 2, that would put in the 2nd shipment if there is one? Or if I could make a field that would be 'Shipment 1 | Shipment 2', that would work just as well.

I cannot have the same order appear on two lines as that would mess up a bunch of analysis that people use for my file.

Thank you for the help!

[Gysbert_Wassenaar]

Well, you could try something like this:

Shipments:

Load

    OrderNo

    ,min(ShipmentNo) as FirstShipment

    ,max(ShipmentNo) as LastShipment

    ,count(ShipmentNo) as NoOfShipments

    ,concat(ShipmentNo, ' | ') as ShipmentNoList

From ShipmentsSource

group by OrderNo;

min and max will only work if the shipment numbers are actually numbers. If they aren't you could try using minstring and maxstring or the First and Last functions.

[Anonymous]

Travis,

You can keep shipements info in a separate logical table, linked to the orders table (by order id), and on the fron end, use concat() function to get the shipment info in one line.

Regards,

Michael