create exception table column from multiple files

[Anonymous]

i have 4 CSV file  which are having common  transcation id,

but in,  

file 1:- 1,2,3,4,5 rows,

file 2:- 1,2,3,6  rows,

file 3:- 1,3,8,7 rows,

file 4:- 1,4,7,8,9 rows.

so, i want to create table column which can be shown exception messsage that  which file data is missing?   

[TRF]

Based on your sample data, can you show the expected result?

[Anonymous]

Thanks @TRF for reply,

i have 4 CSV file  which are having common  transaction id,

but in,  domain of transaction id is 0-9

file 1:- transaction id 1,2,3,4,5 

file 2:- transaction id 1,2,3,6  

file 3:- transaction id 1,3,8,7 

file 4:- transaction id 1,4,7,8,9 

target table:-

file name    missing id

file 1              6,7,8,9

file 2             4,5,7,8,9

file 3             2,4,5,6,9

file 4             2,3,5,6

[TRF · Accepted solution]

You may use a tFixedFlowInput component to build a pseudo-table with row 1, 2, 3, ..., 9.

Then, use tFileList to iterate over the file list, tFileInputDelimited to get the file content, tMap to join with the pseudo-table using an inner join and catch the rejected rows to have the missing transaction Id for each row.

[Anonymous]

thanks sir it works, but in the place of 1-9 row ,if we have milions of rows then what is the process?

[TRF]

Not the same story!

You need to merge all your input files (tFileList-(iterate)->tFileInpputDelimited-->tHashOutput) then remove duplicates (tHashInput-->tUniqRow-->tHashOutput).

This will replace the pseudo-table associated to a tFixedFlowInput in my previous answer.

Then you can continue with the scenario I proposed before.