Add Progressive Number to fileOutputDelimited

Seven Stars

Add Progressive Number to fileOutputDelimited

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:
Example.png

 

 

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

 

Nine Stars

Re: Add Progressive Number to fileOutputDelimited

Seven Stars

Re: Add Progressive Number to fileOutputDelimited

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.

Nine Stars

Re: Add Progressive Number to fileOutputDelimited

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 -

 

tMap-Seq4.JPG

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 



 

Seven Stars

Re: Add Progressive Number to fileOutputDelimited

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)):"        "

 

Seven Stars

Re: Add Progressive Number to fileOutputDelimited

I think I cannot read the value of row6.message_type_id
Nine Stars

Re: Add Progressive Number to fileOutputDelimited

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.

Seven Stars

Re: Add Progressive Number to fileOutputDelimited

I attached my tMap below:

 

Pic1.pngThis the actual result of my .txt:
Pic2.pngActual Result: tFileOutputDelimited

Nine Stars

Re: Add Progressive Number to fileOutputDelimited

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.
Seven Stars

Re: Add Progressive Number to fileOutputDelimited

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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog