topic Re: Need help to retrieve first matched rows for multiple filter in Talend Studio

Need help to retrieve first matched rows for multiple filter

Anonymous — Sat, 16 Nov 2024 03:24:45 GMT

I have 100 files and each contains something like below (there are thousands of rows on one file)
There is no guarantee that both "A" rows and "B" rows exist -- but in most cases we should have "A" rows, we may not have "B" rows sometimes.

20200129 900102 A

20200129 000103 A

20200129 000105 A

20200129 008202 B

20200129 009302 B

20200129 010345 B
20200129 010111 C

20200129 010222 D
...
How could I get the first "A" row and the first "B" row?
The expected result from above file should be:
20200129 900102 A
20200129 008202 B
(They are retrieved only because one is the first row with "A" and one is the first row with "B")
I will need to get them at almost the same time, because I have a logic :
if there is no "A" row, use the first "B" row's value, otherwise get "A" row's value.

If I ONLY need to process one file, I know how to do it: just using tFixedFlowInput to pass value
"A" and "B" into tMap as main, and use the file as lookup, use "A" , "B" value to do the join(match), First Match, Inner join, I should be able to get the first "A" row and/or the first "B" row. But since I have 100 files that I want to process in this same way, I should not pass the hardcoded value "A" and "B" as "main".

Could someone please help me out?

Thanks!

Re: Need help to retrieve first matched rows for multiple filter

TRF — Thu, 30 Jan 2020 06:00:03 GMT

What if you use this within a tJavaRow :
output_row.line = input_row.line;
input_row.line.contains("A") ? ((String)globalMap.get("A")) == null ? globalMap.put("A", input_row.line) : ((String)globalMap.get("A")) :
((String)globalMap.get("B")) == null ? globalMap.put("B", input_row.line) : (String)globalMap.get("B");

At the end, bot A and B global variables should contain what you want.

Re: Need help to retrieve first matched rows for multiple filter

Anonymous — Thu, 30 Jan 2020 06:10:05 GMT

Thank you for the quick feedback!

I don't see how your code would get the FIRST "A" row or the FIRST "B" row.
Did I miss something?

Re: Need help to retrieve first matched rows for multiple filter

TRF — Thu, 30 Jan 2020 06:50:21 GMT

This piece of code set a global variable "A" with the first line which contain A.
It also set a global variable "B" with the first line which contain B.
If you want the first A or B, just use the same variable for both.
After tJavaRow you can use a tFixedFlowInput to start a new flow with these variables to populate the desired fields.

Re: Need help to retrieve first matched rows for multiple filter

Anonymous — Thu, 30 Jan 2020 07:20:27 GMT

Thanks!

I think I have figured out how to accomplish this with a bunch of components
But I will try your code tomorrow.
I still have a little difficulty understanding how the code would give the First "A" and "B".
I don't have much knowledge of Java language and am not too familiar with tJavaRow either -- the only times that I used tJavaRow was to create global variables when there is only ONE row in the input flow. But for my files, there would be thousands of the rows in the input flow, how it only has one output?

Thanks!

Re: Need help to retrieve first matched rows for multiple filter

TRF — Thu, 30 Jan 2020 07:34:31 GMT

Here is the trick:

input_row.line.contains("A") ? ((String)globalMap.get("A")) == null ? globalMap.put("A", input_row.line) : ((String)globalMap.get("A"))

Read it like this:

if current line contains A
  if global variable A is null put current line to A else (because it is mandatory) put current value of A to A

Got it?

Re: Need help to retrieve first matched rows for multiple filter

akumar2301 — Thu, 30 Jan 2020 13:01:21 GMT

Hello did you try taggregaterow with First as aggregate function

Re: Need help to retrieve first matched rows for multiple filter

Anonymous — Thu, 30 Jan 2020 15:27:58 GMT

I used tSampleRow and Range value "1" to get the first row.

Now I realize tAggregateRow-> First probably would be easier for other developers to understand for future support.

But I don't know from performance point, which one would be better, tSampleRow vs tAggregateRow?

Re: Need help to retrieve first matched rows for multiple filter

akumar2301 — Thu, 30 Jan 2020 15:38:02 GMT

I am not sure how did you get correct result based on tSampleRow but i your logic works

tSampleRow will give better perormance.

Re: Need help to retrieve first matched rows for multiple filter

Anonymous — Thu, 30 Jan 2020 16:00:38 GMT

Here is my design:

When reading the files, I used the Stringhandling function to get the "A" or "B" value into a Row_Code field. And then inside tMap, I join Row_Code with Code ( hardcoded as "A" and "B" in tFixedFlowInput).

My real logic should be: get the First A row or First B row, which ever is fine.
Because of the "Inner Join" I used in tMap, & tSampleRow (range "1"), I was able to accomplish that.

I am still doing the test, but it seems to me, the job would not fail if the file doesn't have A rows or B rows at all.

Re: Need help to retrieve first matched rows for multiple filter

akumar2301 — Thu, 30 Jan 2020 16:22:58 GMT

ok got it .

So you have list of row_code for all files to do lookup.
A
B
C
D
.
.

Re: Need help to retrieve first matched rows for multiple filter

Anonymous — Thu, 30 Jan 2020 16:40:09 GMT

The design I completed last night was a little more complicated because I was trying to implement the logic : if there is no "A" row, use the first "B" row's value, otherwise get "A" row's value.

This morning I talked to my colleagues and we confirmed my logic should be:

use First "A" row or First "B" row, either one is fine. So my current job was simplified.

BTW, in the file, the order of the code is random, it could be:
xxxxxxxxG
xxxxxxxxE
xxxxxxxxB
xxxxxxxxA
But the length is always the same and the code is always the last character. The order of the code appears doesn't matter though.