Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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!

Tags (2)
2 Replies

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

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.


talk is cheap, supply exceeds demand
mov
Esteemed Contributor III

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

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

Community Browser