How to use a lookup

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

Posts: 18
Joined: Tue Dec 11, 2018 9:19 pm

How to use a lookup

Postby mohamedosharif » Thu Dec 13, 2018 9:23 pm

Are there any tutorials or resources that explain how to use a lookup in clover? i have a lookup pointing to my database which pulls out "number", i then have a relational join that that pairs said lkp_number=orig_number, what is the SQL that needs to be in the lookup reader? SQL below is what i have:

select number from prod_filing as pf

there really is not a where condition, i want this condition to be true lkp_number=orig_number. i created a metadata that pulls "number" from the lookup which then goes to a relationalJoin and the job condition is set(lkp_number=orig_number), i then have a metadata that goes from the relationalJoin that includes the additional field LKP_number to a filter and i want to filter out where LKP_number is null.

i know i'm asking alot but any videos/documentation would be very useful.

Posts: 24
Joined: Wed Oct 24, 2018 2:38 pm

Re: How to use a lookup

Postby andras.csore » Mon Dec 17, 2018 4:11 pm

Its a little bit unclear what you want to archive. May a little bit clearer description can help.
What lookup type you use? DBLookup?
Note: as far as i remember, for dblookup the 'where x.something = something2' may look like: 'where x.something = ?', and the Question mark is replaced by the value.
(forum member, not Clover support)

Posts: 18
Joined: Tue Dec 11, 2018 9:19 pm

Re: How to use a lookup

Postby mohamedosharif » Mon Dec 17, 2018 9:03 pm

apologies for not being clear and thank you for your reply.

i'm doing a looking on a DB table. i have a port going in with the value "in_arbitrary_string" and i want it to match up with "lkp_arbitrary_string" from the db table. so based on what you said, i'm assuming this is what the SQL would look like.

select "arbitrary lookup" from some_table
where "in_arbitrary_string" = "lkp_arbitrary_string"


also, how can i simply pass through all other columns that come with the lookup?

Posts: 24
Joined: Wed Oct 24, 2018 2:38 pm

Re: How to use a lookup

Postby andras.csore » Tue Dec 18, 2018 3:13 pm

I write the followings more-or-less from my memory, so may i made some typo mistake.
The following describe in main points the case, if you want to get multiple fields data back from lookup.
I write some 'exchange_rate' sample with currency, etc.

Ok, create first a Lookup.
SQL Query:
select *
from some_reference_table
where ref_type = 'CURRENCY'
and ref_id =

=> the '?' is the incoming 'key' what we like to evaluate against lookup.
=> the 'some_reference_table' has 10 field. Fields like: ref_id, ref_type, longname, xch_rate, lastupdate_date, etc.
=> I create metadata for all fields from the 'some_reference_table', metadata name: reference_allfield
=> i name the DBLookup: currency_reference

=> I put some REFORMAT component the followings:

reference_allfield x_currency = lookup(currency_reference).get($in.0.currency);
=> reference_allfield => the metadata name
=> x_currency => the 'variable/collection' where we want to fetch the lookup fields.
=> lookup(currency_reference).get($in.0.currency) => we call the lookup, and pass the 'currency' as a 'key' to update the SQL Select '?'

if (x_currency == null)
{ //Oopppsss... currency not found... problem handling...
$out.0.xch_rate = null;
$out.0.xch_value = null;
$out.0.xch_date = null;
{$out.0.xch_rate = currency_reference.xch_rate;
$out.0.xch_value = currency_reference.xch_rate * $in.0.orig_value;
$out.0.xch_date = currency_reference.lastupdate_date;

=> first we check te returned x_currency is NULL or not. If null, we do the 'error handling' or whatever its needed in this case.
=> if not null (what is good) => We can retrieve the fields from the lookup what we wanted, in here we fetch 3 field.

I like to note:
'best' lookup usage may depend of your data amount / data is sorted or not, your machine memory, you wank lookup key validation, etc.
For example: if your lookup table is relatively small, like 10-40-200 row, standard lookup's just super.
But if you lookup table row count is for example 200000+, and incoming main row count is 10million+, and you want to join lot of fields from 'lookup', may (just may) some other components/solution can provide easier usage, may better performance.
For example: ExtMergeJoin (my favorite); ExtHashJoin ;
With them, you can 'join' easy multiple fields, etc.


Posts: 151
Joined: Mon Feb 29, 2016 5:33 pm

Re: How to use a lookup

Postby vazquezrosariop » Tue Jan 08, 2019 5:50 pm

Hi mohamed,

Andras, explanation is quite accurate. I will agree that is in import to note that if the row count of the Data Base is relatively small then DBLookup works well, anything larger and I would highly recommend using the ExtHashJoin/ExtMergeJoin.
Pedro X. Vazquez Rosario
CloverCARE Support

Visit us online at