How to use a lookup

CloverDX Platform
1

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.

2

Hi,
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.
Andrase
(forum member, not Clover support)

3

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”

right?

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

4

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;
}
else
{$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.

Andrase,

5

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.