Search code examples
vlookuptalend

Talend: tMAP LookUp - fist match after row

Hello I'm facing an issue using tMap component and Vloopup feature. Basically I got as input an excel file, witch I've duplicate the input, one for main row other for lookup: enter image description here

Excel Column (FileInputExcel_1 + FileInputExcel_3):

enter image description here

My Goal is: FileInputExcel_1 is iterating row by row, for EACH FileInputExcel_1 row I need to lookup FileInputExcel_1 COL3 in COL2 RETURN FIRST MATCH FileInputExcel_3 COL1 value FIRST MATCH FileInputExcel_3 ROW >= actual ROW iteration FileInputExcel_1

ie: ROW 1 result should be 61416:

enter image description here

ie: ROW 5 result should be 3225:

enter image description here

I've already tried to play with tMap, but I don't understand how keep the ROW rules: I'm failing always obtaining the first match despite of the cursor (BN_LINE variable is returned only at and of subjob, so cannot use it). Any idea/workaround? Thanks a lot

Solution

  • I've got another suggestion I've changed a bit, working 100% and using only Talend component without JAVA snippet code. enter image description here

    1st, add a column (rank for example) representing the row number in the tFileExcelInput; result buffered into tHashOutput_1

    enter image description here

    2nd, use the tMap to join tHashOutput_1 with itself, filtering on rank, make sure to select All Matches enter image description here

    3rd, eliminate duplicates using tUniqRow