How to obtain a field with the value in a row in the same input file
Hello all, i'm using TOS for Data Integration and i have a problem and after trying multiple options i don't know how i can do this.
I have a tFileList that iterate excel files un a folder. The excel files have a 8 rows header, and after that header, the table with the different columns. I need a data in the 1st row that have the enterprise code, and i need that enterprise code to be repeated as a value in another column of the table below. For example:
Row Column A Column B Column C
1 My enterprise name 545/18
2 Title
3 Date Concept Amount
4 18/05/2019 Table 34.25
5 10/06/2019 Chair 12.75
......
I need a flow with the enterprise code wich is in the 1st row of the excel repeated in every row of the table, like this:
Enterprise Date Concept Amount
545 18/05/2019 Table 34.25
545 10/06/2019 Table 12.75
I have solved this with two tFileInputExcel that in the 1st i only take the 1st row of the file, and after that y use tExtractRegexFields with a regular expresion to obtaib de enterprise code. In the second tFileInputExcel i skip the header rows. After this, i use a tMap to obtain a flow with the complete schema (the Enterprise code, and the other colums).
Where is the problem? The problem is that i need to process a lot of files. So, before all i use a tFileList to iterate Excel files. i send each iteration to the two tFileInputExce in order to process the same file at time. After this, i can't combine in a tMap beacuse i splited the flow before so i use a tHashoOutput and tHashInput in subjobs in order to combine de results but the problem is the HasInput with the enterprises codes has for example 3 enterprise codes and the other HashInput has all the data. If i combine this, the result is a cross join and all enterprise codes will be in all data rows.
¿How can i do this? Please help.
Thanks,
Marcos.
2 The iterate flow pass throw a tFileInputExcel component
You could execute the tFileInputExcel reads in sequence rather than in parallel. If that degrades performance too much, you could add a column containing the filename to both flows before storing the data in the hash. At the end, joining the enterprise code to the rest of the data using the filename to match should prevent a cross join.
You could execute the tFileInputExcel reads in sequence rather than in parallel. If that degrades performance too much, you could add a column containing the filename to both flows before storing the data in the hash. At the end, joining the enterprise code to the rest of the data using the filename to match should prevent a cross join.
