Filter file based on records on table

[Karuetl]

I have 10000 rows for example from table 

And I have another flow which is input file and i want to filter out those ID (From above step) that are present in the file  

Example 

--------------

Table A 

Select ID from Table A ;

123

672

888

Input File

abcve,672,newjersey

jkuijk,999,california 

output should be 

File

jkuijk,999,california 

[David_Beaty]

Hi,

Depending on the database in use, upload the ID from the file into a temporary table and then select the rows from the database table A where they do/do not exist in the temporary table.

Thanks

David

[Karuetl]

There are multiple files with different schema among each and those has ID 

I want to each file check with those ID from the table and then filter out 

[Ganshyam]

Hello,

Try performing  joins on table and input file.

Apply filter on second tmap and filter the values that have null values.  out1.ID_1 == null 

Regards

Ganshyam Patel

[Anonymous]

@Karuetl @Ganshyam

You can do it in single tmap as shown below (you can remove tLogrow since it has been added for testing) 

The output is as shown below.

The trick lies in using the inner join and catching the rejected inner join records from the tMap.

If the answer has helped you, could you please mark the topic s resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

[Karuetl]

What is the join condition logic 

[Karuetl]

You have declared the column name and you know the position , in my case I dont know the position and i read the file entire row as string

[Ganshyam]

Hello,

You can use tfileinputfullrow component to read entire row as a string and then split into 3 field using textractdelimited component and further you can continue with joins.

[Karuetl]

As i mentioned its dynamic columns , it is not fixed like 3 columns or so 

one file can have 10 columns other file can have 6 columns

[Ganshyam]

Hello,

Not sure , but you can try using tjavarow and some java code to handle the dynamic input column.

Regards

Ganshyam Patel