topic Re: tFileInputMSPositional join data between records in Talend Studio

tFileInputMSPositional join data between records

Anonymous — Sat, 16 Nov 2024 03:11:42 GMT

Hello,

I'm using the tFileInputMSPositional to read a file that contains different positional records. In this file, exist some records that need to be joined with others, because one is the main and the others are like details of this one. Does anyone know how I can do this?

Thank you!

Re: tFileInputMSPositional join data between records

TRF — Sun, 23 Feb 2020 14:57:48 GMT

Redirect each record type to a dedicated tHashOutput.

Start a new subjob with the tHashInput associated to the detailed or children records, add a tMap with the tHashInput related to the main or parent records as the lookup.

Add an inner join in the tMap using the appropriate fields and map the desired flieds to the output flow.

This is how the job should look like:

Suppose you have the following content:

Record type is identified with 1rst character for all records (1 = parent, 2 = child).

2nd field is parentId (length 2) for all records.

3rd filed is parentName for parent records and childId for children records.

This is defined like this in tFileInputMSPositional component:

The tMap component is in charge to join both parents and children records to produce an output with parentId, childId and parentName fields:

And the result is as expected:

.--------+-------+----------.
|         tLogRow_1         |
|=-------+-------+---------=|
|parentId|childId|parentName|
|=-------+-------+---------=|
|10      |100    |Name 10   |
|10      |101    |Name 10   |
|10      |102    |Name 10   |
|20      |200    |Name 20   |
|20      |201    |Name 20   |
|20      |202    |Name 20   |
'--------+-------+----------'

Re: tFileInputMSPositional join data between records

Anonymous — Sun, 23 Feb 2020 16:37:40 GMT

Thank you @TRF,

I think this gone work for me. I will try your solution and give a feedback if it works.

Re: tFileInputMSPositional join data between records

TRF — Thu, 27 Feb 2020 07:33:26 GMT

Did this work?

If so thanks to mark your case as solved (Kudos also accepted).

Re: tFileInputMSPositional join data between records

Anonymous — Fri, 28 Feb 2020 03:51:21 GMT

Hello @TRF,

Yeah, it works.

One more thing. This solution is good for large amount of data? The files that I will receive contains more than 10 million of register with several columns (one type of register has 100 columns for example). It will be a copybook data, and I will need to load this file daily.

Thank you!

Re: tFileInputMSPositional join data between records

TRF — Fri, 28 Feb 2020 06:48:32 GMT

It should work but I've never tried with very huge files. If you encounter memory issues, try to change Xmsx JVM parameter for the job or replace tHashOutput/input by delimited files.

Re: tFileInputMSPositional join data between records

Anonymous — Fri, 28 Feb 2020 14:57:29 GMT

Ok.

Thank you for the quickly reply @TRF.