[SOLVED] Informatica: How to filter a table based on aggregation result of another table?

Informatica: How to filter a table based on aggregation result of another table?

In Informatica, how to filter a table based on the aggregation result of another table?

Given the following 2 tables:

Table_1: SomeText VARCHAR, SomeDate DATE

Table_2: SomeDate_2 DATE, OutputDate_2 DATE

and the following aggregation (into output port "MinDate") of Table_2:

SELECT MIN(OutputDate_2) AS MinDate FROM Table_2 WHERE SomeDate_2 BETWEEN 01/01/2023 AND 02/01/2023

how can Table_1 be filtered on SomeDate >= MinDate?

If I can add a port/column in Table_1's source qualifier with MinDate, I can use that to filter Table_1. But not sure if that's possible.

Solution

You can have another Source Qualifier, do a Full Outer Join and use a Filter Transformation.

Src1-SQ1---Jnr--Flt--Tgt
          /
Src2-SQ2-/

In such case you need to pay attention to the way you join, as it might produce a lot of duplicated rows if not done carefully.

Another option is single Source Qualifier with two Source Transformations connected and a SQL Override. This is pretty much the same thing you did, but it will clearly indicate the use of both sources at the first glance at the mapping.

Src1-SQ1---Jnr--Flt--Tgt
     /
Src2/