Which Lookup should I Use?

CloverDX Platform
1

We load a flat file into our database, the flat file has a unique column flat_file.Column_1 which we use to check against the database (database_table.Column_2) to see if the information already exists. I want to do a full outer join, so basically if flat_file.Column_1 = database_table.Column_2, i don’t want to load the data, i only want to load the data where flat_file.Column 1 != database_table.Column 2.

I’m not sure if there’s a way to do a lookup and just return the values that aren’t equal. I was thinking about doing a full outer join and creating a condition after the join that only passes through null values for Column 2.

However, I’m having trouble using the LookupJoin(assuming that is the right one i should be using). I defined the lookup key, and lookup table.

Query for lookup table

Select database_table.Column_2
from Database_table;

Lookup Key: Flat_file.Column_1

Where in the lookup do i define the sql?

basically want this;

select * from Database_table
where Database_table.Column_2=flat_file.Column_1

::Disclaimer:: I come from an Informatica background and thus the sql above is similar to what we’d use in a lookup.

Any help is appreciated.

2

Hi,

I would rather suggest a solution where you would use a DBInputTable to search the database table for:

select * from Database_table
where Database_table.Column_2=flat_file.Column_1

and if it returns zero records, write the flat file into the DB.

Searching in the database should be quite a lot faster than loading all the data from it into the lookup table and searching that. Please correct me if misunderstood your scenario.

Best regards.