Talend: tMAP LookUp - fist match after row

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
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!

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now