Five Stars

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: 

Excel Column (FileInputExcel_1 + FileInputExcel_3):

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:


ie: ROW 5 result should be 3225:


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
2 REPLIES
One Star

Re: Talend: tMAP LookUp - fist match after row

Hi,
I got it. Maybe a better solution exists, but this one works fine.

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

2nd, use the tMap to join tHashOutput_1 with itself, filtering on rank

3rd, eliminate duplicates using tUniqRow
Must be validated with more complex data sample.
That's all.
Ragards,
TRF
Five Stars

Re: Talend: tMAP LookUp - fist match after row

After modify match model to "Unique Match" is works 100%
Very useful, thank you!