Query about look up joins - Join keys

CloverDX Platform
1

Dear Forum Members

I have a look up table which i have created and i have two join keys i.e. “SupplierID” and “ProductNo”.

When we do look up join do we have to have the same name column to do the join? I am having an issue as I have different datasets and within same dataset the column header can be “SupplierNumber” instead of "SupplierID and “ProductNumber” instead of “ProductNo” - is this acceptable?

Please if you can kindly advise

2

Hi,

Not sure if I understand your question correctly, but it seems you are asking whether if lookup table has key defined as field1, field2 then in your LookupJoin, the record you are trying to “join” with values from lookup table has to have fields1 and field2 ?

The answer is NO. As part of LookupJoin configuration, you define the “key mapping” where you defined what field(s) from your input record will be matched against your lookup defined key(s).

This is an example of such usage (XML serialization of that config):
<Node guiName="LookupJoin" id="LOOKUP_JOIN" joinKey="$fieldA=$field1;$fieldC=$field2" lookupTable="LookupTable0" type="LOOKUP_JOIN"/>

Here, you defined that input record’s fieldA will be matched against lookup’s key field1 and fieldC against field2.

This can be, of course, configured in the LookupJoin edit component dialog Join Key property.

If your question is about something else, then please explain.

3

Hi David, Thank you very much for your response, Accept my apologies if my question is not clear.

My query is regarding, I have created a look up table using Field1, Field2 for example “SupplierID” and “ProductNo” - lookup table “SupplierData” created but when I do use them using LookupJoin - Field1 and Field2 will have the same column values but incoming dataset data joining to Lookup join contains different column names which i am defining using Join keys i.e. instead of “SupplierID” it is “SuppliedNumber” and instead of “ProductNo” it is “ProductNumber”, is it fine? as I am having issues with my data not returning any results. I hope this clarify this.

4

Hi,
I have attached a simple CloverDX demo project. There is one graph using LookupJoin with some dummy data. Just import it into Designer as a new project.
It attempts joining input data against lookup table with some matches and some misses. The input data and lookup have different field names - using those names you posted above.

LookupJoinDemo.zip (5.8 KB)

Thinking about your case the problem could be that values you are trying to match against each other are not trimmed (I assume you use string data types for those fields).