Join input and lookup file

One Star

Join input and lookup file

I have requirement and need solution how can we do this in Talend.
input data as in column level and lookup as in row level, need to join both and get the respective Average to populate score
Input:
ID      C1  C2 C3 C4 C5 C6
X123    0   1   0   1   1   0
B456    1   0   1   0   0   0
lookup:
CODES  AVERAGE
C1         1.50
C2         0.50
C3         2.50
C4         4.50
C5         3.50
C6         1.50
Output:
ID        SCORE
X123      8.5
B456       4
for each input columns (C1, C2...)get the average from lookup and multiply to input column values and then add all to get the score
EX: for row X123      SCORE = (0 * 1.50 + 1 * 0.50 + 0 * 2.50 + 1 * 4.50 + 1 * 3.50 + 0 * 1.50) 
Please help me in this case.
Thanks, 
Kyle
Sixteen Stars

Re: Join input and lookup file

Is your input limited to ID and C1 to C6? Will you ever get a C7? If so, this is very easy to do with a tMap. You have your input and a completely unconnected lookup with your lookup data. What will happen will be that for every input row, 7 rows of lookups will be returned. You can then use your tMap variables to add the lookup values based on the 0 or 1 from your input data. Of course, this will output 7 rows for every input row if you don't put a mechanism to prevent this. So either use filtering in the tMap to only return the last row for each group (ID being the group key) or use a tAggregateRow to do this after the tMap.
This is kind of similar to a tutorial I put together using a tMap and tMap variables to sort a dynamic column order problem. It is not exactly the same, but this might give you some ideas on how to implement this (along with the description above)...
https://www.rilhia.com/tutorials/dynamic-column-order
One Star

Re: Join input and lookup file

Thank you for your reply rhall !
I have input data columns more than 100 (C1, C2....C125) and same equivalent values presented in lookup too. Please let me know if there is any dynamic way to achieve this ?
Regards,
Kyle
Sixteen Stars

Re: Join input and lookup file

Are you using the Enterprise Edition of Talend or an Open Source version? If you are using the Enterprise Edition, I assume you plan on using the dynamic schema for the varying number of columns in your input. Or are there always 125 + the ID column in your input? We need to establish this before going much further in detail.
One Star

Re: Join input and lookup file

I'm using the Enterprise Edition..input and lookup fields will be static and i have 125 columns in inputs (C1, C2....C125)
i'm trying to use the tSetGlobalVar component to keep all the lookup values and then use the globalMap.get in tMap to fetch the AVERAGE.

tSetGlobalVar----onsubjobok----> inputfile-----> tmap----->outputfile.
in tmap vatiable section placing below statements and getting respective AVERAGE
(String)globalMap.get("C1")
(String)globalMap.get("C2")
(String)globalMap.get("C3") ...etc

it's working fine but when doing multiplying of C1 (from input) and AVERAGE (from C1 lookup) getting error that can't convert string to interger. any thoughts on this 

Thanks,
Kyle

      
Sixteen Stars

Re: Join input and lookup file

OK, I think I understand what you are doing and if I understand it correctly it seems reasonable. 
Your error is caused by how you are storing the values in your lookup. It appears you are storing them as String values (or at least you are casting them to String when you retrieve them from the globalMap). You either need to store them as something like a Double or consider converting them to a Double when you retrieve them.
I should say that with the globalMap you are actually storing all values as Objects. What happens is you add a value (lets say an Integer) and it is stored. When you retrieve it you must tell the system what Java class type it was. Once you have retrieved the value as the correct type, you can then go about converting it to another type if that is what you want. Check out "casting" in Java.
One Star

Re: Join input and lookup file

globalMap objects stored as double and it's working fine....Thank you rhall !
Just question, is there any way to store the all lookup values in tSetGlobalVar component without typing each and every key & value ?
since i have 125 rows in lookup file, please let me know 
Thanks,
Kyle