Talend community forum / [resolved] tFilter Advanced Mode

Piero001 · 2012-06-04 11:31:11

Hello community,

I have a problem with the tFilterRow component. I don't know how to give it the following command:

If "Vorgang" is "Cancel" than do not integrate "Betrag" of "Artikel" with the same "Nummer"

-> I just want to aggregate all "Artikel" which are not cancelled!

So Talend should filter the value of "Nummer" whos "Vorgang" is "Cancel".

Pls look at my screenshot. Do you know a JAVA code for this Command?

Regards,

Piero

Uploaded Images

pedro · 2012-06-04 11:58:09

Hi

You might create a job as the following images.

The first subjob will generate a csv file called "out.csv".
The second subjob inner join source file and out.csv in tMap.

Regards,
Pedro

Uploaded Images

Piero001 · 2012-06-04 12:31:59

Hi Pedro,

thank you very much for your help.
I think your job is very good. But i get a nullPointException because the out file has just the value "1" or nothing in the "Storno" column.

How do I handle that if Storno is set as Integer?

Regards,

Piero

pedro · 2012-06-05 03:39:34

Hi Piero

I'm sorry. I can't find a column called "Storno" in your example.

Regards,
Pedro

Piero001 · 2012-06-05 10:44:12

I'm sorry pedro, I meant the "Cancel" column

pedro · 2012-06-05 10:57:02

Hi

Why do you have to set Cancel as Integer?
If you set it as String, you can avoid NPE exception.

Regards,
Pedro

avdbrink · 2012-06-05 11:11:54

Hi Piero,

I think there might be an easier way to solve your problem.

I've attached some screenshots that result in a correct result I suppose.

Hope it helps (again )

Best regards,
Arno

Uploaded Images

Piero001 · 2012-06-06 16:20:11

Thank you a lot for your help, the community is great!

I thing we are on the right way but i have to explain the problem more detailed:

I just want to ignore each Numbers which have a "cancel" in Vorgang. Maybe it helps you when I say that number stands for receipt Number.

the job was created to calculate the earnings. It should recognize that a receipt is canceled and do not consider it in the calculation. I can't take for grant that "Cancel", if it occurs, is the last element of a single receipt because I don't know if the shema of the file which is ~3 Gbyte big is constantly equal. I also tried what pedro told me but using a pivot is very slow with big Files..

So now I'm searchin for an Expression, that sounds like this:

Filter the key attribut "row.Number" if "row.Vorgang" of the same Number equals to "Cancel"

Maybe you can help me.

Regards,

Piero

avdbrink · 2012-06-06 17:16:08

Hi Piero,

I didnt't know the "Cancel" row could be anywhere in the sequence of rows, so I used the "Last" function in the first aggregation. I've changed the job a little now. It uses the List function on the Vorgang column, which creates a comma separated list of values occurring in the Vorgang column. In the Filter component all records containing Cancel in this Vorgang column will be filtered out again, now using a String.match method with (.*)Cancel(.*) as parameter.

See the attached screenshots for more info.

I suppose this should do the job.

Regards,
Arno

Uploaded Images

Piero001 · 2012-06-06 17:47:58

Hey Arno, thank you very much for your answer.

I tried your idea but now it gives me an error log:

Warning:the operation 'list_object' for the output column 'Vorgang' can't be processed because of incompatible input and/or output types
Exception in component tAggregateRow_1_AGGOUT
java.lang.NumberFormatException

I set
Number as Integer, Vorgang as String and Betrag as BigDecimal. What did I wrong?

Regards, Piero

avdbrink · 2012-06-07 08:09:07

Hi Piero,

I see where that's going wrong
You should configure the first tAggregateRow component to use "List" as function for outputcolumn Vorgang and not "List(object)".

Good luck!

Arno

Piero001 · 2012-06-11 11:00:06

Thank you arno! This did help

Announcement

Unanswered posts

#1 2012-06-04 11:31:11

[resolved] tFilter Advanced Mode

#2 2012-06-04 11:58:09

Re: [resolved] tFilter Advanced Mode

#3 2012-06-04 12:31:59

Re: [resolved] tFilter Advanced Mode

#4 2012-06-05 03:39:34

Re: [resolved] tFilter Advanced Mode

#5 2012-06-05 10:44:12

Re: [resolved] tFilter Advanced Mode

#6 2012-06-05 10:57:02

Re: [resolved] tFilter Advanced Mode

#7 2012-06-05 11:11:54

Re: [resolved] tFilter Advanced Mode

#8 2012-06-06 16:20:11

Re: [resolved] tFilter Advanced Mode

#9 2012-06-06 17:16:08

Re: [resolved] tFilter Advanced Mode

#10 2012-06-06 17:47:58

Re: [resolved] tFilter Advanced Mode

#11 2012-06-07 08:09:07

Re: [resolved] tFilter Advanced Mode

#12 2012-06-11 11:00:06

Re: [resolved] tFilter Advanced Mode

Board footer