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?

[oknotsen]

The lines in the 2nd chart: Do they contain the same data for every record or do you want to sum/count those values to be 1 value?

Maybe you can supply us with some demo data?

[hic]

First, you should read Don't join - use Applymap instead‌. It describes a method that uses only one value.

Then you need to answer the question: If there are three records in Table 2 that match - which record should you use? The first? The last?

HIC

[#4]

‌thanks for your reply.

it is very likely applymap is my answer I will carefully read through this first. I doesn't matter which to take ymthe are identical.

[#5]

‌exactly the same so want it to work as vlookup. Group by doesn'&t work on 15mio records...

[#6]

I have read trough Don't join - use Applymap instead however I am not getting it working. Probably because I am not the most experienced Qlikview user.

MapCustomerIDtoCountry:

     Mapping Load CustomerID, Country From Customers ;

CustomerID and Country are here variables that appear in table 'Customers'? Where is the table customers? Is that above this script line or is this a file you are referring to that hasn't been loaded yet in the script.

In my situation this would look like:

Mapping Load [Shipment Number2], SKU From FULLOUTBOUND_CALC_GROUPBY.qvd;

Where [Shipment Number 2] and SKU are both variables in the table FULLOUTBOUND_CALC_GROUPBY.qvd which has not been loaded yet.

When this is done the next was;

Orders:
     Load *,

          ApplyMap('MapCustomerIDtoCountry', CustomerID, null()) as Country
          From Orders ;

Orders is a table that also exist yet or? Where does it come from?

In my situation I would assume this is the table where also Shipment Number2 is in and where I would like to hook SKU on.

FINALFULLOUTBOUD:
     Load *,

ApplyMap('MapShipment Number2toSKU', [Shipment Number2], null()) as SKU

From FULLOUTBOUND_CALC_GROUPBY_2.qvd;

Where FINALOUTBOUND is the name of the table I want to end up with and FULLOUTBOUND_CALC_GROUPBY_2 is the file which is not loaded yet and contains the [Shipment Number2].

Long story short just to be sure we are on the same page here.

In table 1 there is variable 1 and only variable 1 which are some 4.800.000 100% unique values.

In table 2 there are some 40 variables of which 1 is matching in table 1. However table 2 has maybe 3 to 5 identical lines 100% identical lines. Which results in a data set of 15.000.000 milion lines.

What I want to end up with is 1 table with 4.800.000 rows/lines with 40 variables on each line.

Just as with a vlookup it would be perfect to hit the first record and no more after.

Key Variables are;

1. The unique value in both tables, which is [Shipment Number2]

2. The name of the file with only [Shipment Number2] = FULLOUTBOUND_CALC_GROUPBY_2.qvd or the name of the table when already loaded in the script Unique_Lines

3. The name of the file with all the 40 variables and unique variable [Shipment Number2] = FULLOUTBOUND_CALC_GROUPBY.qvd or the name of the table when already loaded in the script FULLOUTBOUND_CALC

Normally I could just do a groupby on the FULLOUTBOUND_CALC_GROUPBY however with 15.000.000 lines and 40+ variables my laptop doesn't cope.

I hope I am making my self clear and you can help me with this information how to set up this vlookup.

[Gysbert_Wassenaar]

MapCustomerIDtoCountry:

    Mapping Load CustomerID, Country From Customers ;

CustomerID and Country are here variables that appear in table 'Customers'? Where is the table customers? Is that above this script line or is this a file you are referring to that hasn't been loaded yet in the script.

Customers is the name of a source table. The code is just example code and won't work in real life. If a connection to an sql database is established first then MAPPING SELECT CustomerID, Country FROM Customers; would work if that database contains a table named Customers. If instead there exists a qvd file with the source table then MAPPING LOAD CustomerID, Country FROM Customers.qvd (qvd); can be used.

As to your original problem... I'm not sure what you want to do. How many fields from table 2 do you want to add to table1?

[#8]

Clear on your first answer, I have that working now.That means no errors regarding script when run. However no matches yet on the values only null() matches.

Regarding your question... All of them

[#9]

Mapping Load [ShipmentNumber2], SKU

From

FULLOUTBOUND_CALC_GROUPBY.qvd

(qvd);

Mapping Load [ShipmentNumber2], Variable1

From

FULLOUTBOUND_CALC_GROUPBY.qvd

(qvd);

FINALFULLOUTBOUD:

Load *,

ApplyMap('MapShipmentNumber2toSKU', [ShipmentNumber2], null()) as SKU,

ApplyMap('MapShipmentNumber2toVariable1', [ShipmentNumber2], null()) as Variable1

From

FULLOUTBOUND_CALC_GROUPBY_2.qvd

(qvd);

DROP Table Unique_Lines;

DROP Table FULLOUTBOUND_CALC;

Results in;

With;

I am probably explaining totally wrong... But don't know how else to explain...

[#10]

I am simply extremely bad in copying others examples

MapShipmentNumber2toSKU:

Mapping Load ShipmentNumber2, SKU

From

FULLOUTBOUND_CALC_GROUPBY.qvd

(qvd);

MapShipmentNumber2toVariable1:

Mapping Load ShipmentNumber2, Variable1

From

FULLOUTBOUND_CALC_GROUPBY.qvd

(qvd);

FINALFULLOUTBOUD:

Load *,

ApplyMap('MapShipmentNumber2toSKU', [ShipmentNumber2], null()) as SKU,

ApplyMap('MapShipmentNumber2toVariable1', [ShipmentNumber2], null()) as Variable1

From

FULLOUTBOUND_CALC_GROUPBY_2.qvd

(qvd);