Join or Keep? Multiple records in table 2 should result in 1 row

[#1]

If the Key in Table 1 = 1,2,3

and I have a Key in Table 2 that is 1,2,3

But in Table 1 each key appears only once and in Table 2 it appears around three times per record. I want only the table 1 enriched with table 2 data and not more data lines created than table 1 initially had.

If that is what you wanted to know aswell... Any thoughts on this yet?

[#2]

Hi Henric,

It works but it is a far to heavy task with this number of lines. The script stops after 10 variables, so unfortunately not enough.

Any other ideas how to lookup. Or link the two tables without creating multiple identical lines.

Tnx

[#3]

No solution yet, the apply map was definitely to intense to run.

https://www.wetransfer.com/downloads/7af61603f4091e78859c47cbdd49305020160423085510/6a3f0e37a92e5070...

I don't know how to add the file directly in this forum (maybe I don't have enough credits yet).

[Gysbert_Wassenaar · Accepted solution]

If you want to join all the fields from table2 to table one then you're better off with a join. The alternative is a lot of mapping tables and applymaps or something complicated involving concatenating all the fields of a record together in the mapping table and splitting that field again in the target table.

Try something like this:

Table1:

LOAD ShipmentNumber2, ...other_fields... FROM ...table1_source... ;

LEFT JOIN (Table1)

LOAD DISTICT ShipmentNumber2, ...all_other_fields_that_you_want_to_join... FROM ...table2_source... ;

[#5]

Oke so we are getting closer and it almost appears to be the solution

DISTINCT_SHIPMENT_ID:

LOAD

  [ShipmentNumber2]

FROM

FULLOUTBOUND_CALC_GROUPBY_2.qvd

(qvd);

LEFT JOIN (DISTINCT_SHIPMENT_ID)

FINALOUTBOUND:

LOAD Distinct

     [ShipmentNumber2],

     Order_Type,

     ...,

     StatusDescriptionSimple

FROM

FULLOUTBOUND_CALC_GROUPBY.qvd

(qvd);

During the load fase we first have table 1 (DISTINCT_SHIPMENT_ID) with some 4,6 mio records and than table 2 (FINALOUTBOUND) with 15 mio records or so.

We end up with a total of 10 mio. So it appears it works however it appears some of the line are not as unique on all variables as I hoped.

So the search for the variable that is not equal has begun... Or am I missing something?

Because if change table FINALOUTBOUND with only the ShipmentNumber2 variable I do end up with 4.6 mio records...

[Gysbert_Wassenaar]

So the search for the variable that is not equal has begun... Or am I missing something?

I'd say you are correct.

[#7]

If only I knew friday morning, my friday night and saturday would have been so much more pleasent

Thanks