tMap : complexe inner join and complexe left join

[Alpha549]

Hello everyone,

In the majority of cases, I believe that when we use a tMap to join rows, join is made with equality.

However, for some joins, the condition can me more complexe, for instance : row1.id >= row2.id && row1.id <= row2.id

HERE ARE 2 CASES : A COMPLEXE INNER JOIN, AND A COMPLEXE LEFT JOIN

COMPLEXE INNER JOIN

 

The tMap is configured on Join Model : Inner Join.

The output, as expected :

 

I TRIED TO GET THE SAME RESULT, BUT AS LEFT JOINT : I SHOULD GET A THIRD ROW 8, NULL, NULL, NULL

I changed the tMap join model from Inner Join to Left Outer Join.

The result is the same. I understand that because as I use a filter here, other rows are removed.

Here is how I proceeded :

 

FIRST TMAP :

 

FILTERED : COPYOF...1

 

The other file is COPYOF...2 AFTER A TUNIQUEROW

I USE CSV FILES TO STORE TEMPORARY DATA

 

THEN, I DETECT THE ROWS OF COPYOF...2 (PREVIOUSLY INSERTED IN A CSV FILE) WHICH ARE NOT IN COPYOF...1 (PREVIOUSLY INSERTED IN A CSV FILE) : INNER JOIN AND I GET THE REJECTED

AND SEND THEM IN A THIRD CSV FILE, HERE TO STORE TEMPORARY DATA

  

As I can't include more that 10 image, I continue in the first comment.

[Alpha549]

 

THIRD SUB-JOB, I CONCATENATE THE TWO FILES, AND I GET WHAT I WISHED :

 

Can you give me some advice on how to proceed, and if there is an easier way to achieve a "complex left join"?

[anselmopeixoto]

Hi @Alpha549 Alpha549​ 

What if you try the following:

1. Keep the lookup as inner join just like in your example;
2. On the output section, click on the "add output table" icon with a plus sign;
3. Select "Create join table from" and select "out2" as its source. This will create another mapping based on out2 schema;
4. On this new output mapping, click on the "tMap settings" icon with a wrench tool;
5. Set the "Catch lookup inner join reject" option to true;
6. Map the output columns just like you did on the first schema;

These steps allows you to create "virtual" schemas that are joined to a single output row, so you'll need to connect only "out2" to an output component (in fact, you'll notice there will be only "out2" row when you try to connect tMap to another component).

I hope this helps you achieve the complex left join you need.

[Alpha549]

Hello @Anselmo Peixoto​ ,

Thank you a lot for your answer.

Could you show some screenshots from step 3 ?

Please.

[Alpha549]

@Anselmo Peixoto​  ?

[anselmopeixoto]

Hi @Alpha549 Alpha549​ , here it goes:

Add output table:

 

Create join table:

 

Click on tMap settings for the new output you just created:

 

Enable Catch lookup inner join reject:

 

Connect tMap original output schema to the output component:

 

[Alpha549 · Accepted solution]

Hello @Anselmo Peixoto​ 

Thank you a lot. I didn't know this feature.

Beyond the tips you gave me, I found a solution to make a left join with a complexe join condition. I share with the community.

Here we go :

The final job :

 

I selected commons just to remove the alert. Then, in advanced settings :

import java.util.HashMap;

TMAP :

Nothing in the expression field, as we use the lookup table filter for our complexe join (not a simple equality test).

All rows, INNER JOIN (even if we want to do a left join, it's always inner in the case of complexe join condition)

We create the first output.

Then, the tips you gave me @Anselmo Peixoto​ : we create a second output using the + icone on the upper right part, and we select join.

Create a join table :

 

As seen before, for the join table, we only set the value of AID. Nothing more.

We create a new column called JOINED.

(CONTINUE IN THE NEXT COMMENT)

[Alpha549]

For the out1 schema, we set JOINED to true.

For the join table, JOINED to false.

For the join table, we set catch lookup inner reject to true.

Then, we sort the rows on JOINED value : to get joined rows BEFORE null ones.

TJAVAFLEX with its schema and code :

 

In the output we add a column called VALIDE (for instance).

Start code, we create a HashMap which records if a AID is already encountered.

Main code : we consider that the line is not valide.

if the row is joined, we remember that this AID is encountered, and the line becomes valid.

If the row is not joined, we check if this AID has already been encountered. If not, the row is valid => we keep it.

Then we filter the row :

 

Then we keep unique rows only :

 

When we run the job, we get what I wanted : a left join output based on our complexe join condition, which is not a simple equality test.

 

I hope this will help !

Thank you again @Anselmo Peixoto​ !