topic Re: Add Progressive Number to fileOutputDelimited in Talend Studio

Add Progressive Number to fileOutputDelimited

Anonymous — Sat, 16 Nov 2024 07:49:13 GMT

I have the following issue: I am taking records from several tables and then I combine them with tUnite component in order to create a .txt file. After that I sort retrieved rows based on a certain field. The output file is like this:

                   myField  my2ndField ... ...              
HeaderRecord       xxxxx
DetailRecord       xxxxx
DetailRecord       xxxxx
AdditionalRecord   empty   -> field I want to calculate for Additional Record
DetailRecord       xxxxx
DetailRecord       xxxxx
AdditionalRecord   empty   -> field I want to calculate for Additional Record
DetailRecord       xxxxx
AdditionalRecord   empty   -> field I want to calculate for Additional Record

This is my job:

What I want to do is to calculate "myField" just for "AddionalRecord" (TAB3). This Field is a Progressive number which must works like showed in the screenshot below.

How can I achieve my goal after I merge records with tUnite?

                   myField  my2ndField ... ...            
HeaderRecord       xxxxx
DetailRecord       xxxxx
DetailRecord       xxxxx
AdditionalRecord   000000004   -> field I want to calculate for Additional Record
DetailRecord       xxxxx
DetailRecord       xxxxx
AdditionalRecord   000000007
DetailRecord       xxxxx
AdditionalRecord   000000009

Re: Add Progressive Number to fileOutputDelimited

iamabhishek — Mon, 13 Aug 2018 11:43:38 GMT

Have you tried this approach - https://community.talend.com/t5/Design-and-Development/rolling-or-cumulative-sum/td-p/39850

Re: Add Progressive Number to fileOutputDelimited

Anonymous — Mon, 13 Aug 2018 11:57:52 GMT

No, I did not. I looked it, but I does not seem the solution to my issue. I need a progressive for additional row and I write it to file.

Re: Add Progressive Number to fileOutputDelimited

iamabhishek — Mon, 13 Aug 2018 12:45:08 GMT

Ok, from what I was able to figure out something like this would match your needs.

Before outputting the final output to your file make it pass through tMap and work on the below logic -

So, you would be generating a sequence but displaying it only when the field value is null ( or empty or in whatever form you are getting the data).

Relational.ISNULL(row1.myField) ? String.valueOf(Var.var1) : row1.myField

Re: Add Progressive Number to fileOutputDelimited

Anonymous — Mon, 13 Aug 2018 13:22:56 GMT

I tryied to do something like that, but with a table as output and it worked. The problem was that if I have more than one table with additional record it does not work anymore.

Anyway, as you suggest it almost worked.

Record is added to all fields and on the contrary I want to add it only for those one where row6.message_type_id!="0100" . I link tMap to fileOutputDelimited by using the following condition in myField:

row6.message_type_id!="0100"?String.format("%08d",Numeric.sequence("s3",2,1)):"        "

Re: Add Progressive Number to fileOutputDelimited

Anonymous — Mon, 13 Aug 2018 13:32:17 GMT

I think I cannot read the value of row6.message_type_id

Re: Add Progressive Number to fileOutputDelimited

iamabhishek — Mon, 13 Aug 2018 13:53:32 GMT

Could you help me with some sample data so that I would be able to understand the situation a bit more.

Try adding the sequence to tMap variable and then refer it to your row6.message_type_id based on the condition that you have defined.

Also, when you connect your tMap to tFileOutputDelimited I believe it would not be row6 as this is the input to tMap but not the output from tMap. It definitely is something else that you have created as part of your output table inside tMap.

Re: Add Progressive Number to fileOutputDelimited

Anonymous — Mon, 13 Aug 2018 14:25:51 GMT

I attached my tMap below:

This the actual result of my .txt:
Actual Result: tFileOutputDelimited

Re: Add Progressive Number to fileOutputDelimited

iamabhishek — Mon, 13 Aug 2018 17:01:10 GMT

Why don't you replace the Numeric.Sequence that you have used in your condition as part of your field to update with the tMap variable that you have created.
You should be referring the Var.var1 into your field to update condition.

Re: Add Progressive Number to fileOutputDelimited

Anonymous — Tue, 14 Aug 2018 08:52:09 GMT

I did it. But I have got the same result. Nothing changed