[resolved] Combining records whose text field continues from one to the next.

One Star

[resolved] Combining records whose text field continues from one to the next.

My legacy database uses multiple ordered records to store text descriptions whose length exceeds the field length. Each record has a text sequence number. So a text description spans multiple records which are identical to one another except for the text and sequencing fields. I believe that was a common technique up to the early 1990's.

To upgrade to modern methods, I am trying to use the tjavaflex component to combine those groups of records that contain sequenced text.

It works really well, except that I get a record with null fields at the beginning. And the last record is not transferred.

The idea of the following code is to output the *previous* record only when you are sure there are no more text records to combine. That is when the sequence number of the current record comes back to 1.

Would a kind hearted java expert advise me on how to get that last record?

// start part of your Java code
//Part 1
//initialize "previous loop" variables
String prevEiacodxa = "";
...
String prevFmnnarbg = ""; // this is the text field that spans multiple records in sequence
int prevTexseqbg = 0; // this is the sequencing field
int count = 0;

//Part 2 Main code
// If this is a text extension of the previous record (i.e. seq > 1)
// then no output, just concatenate the text
if (row3.Texseqbg > 1) {
prevFmnnarbg = prevFmnnarbg + row3.Fmnnarbg;
}

//output *previous* record if current sequence is 1
//the second condition in the if statement prevents output when on record 1
if ((row3.Texseqbg == 1) && (count >= 1)) {
row4.Eiacodxa = prevEiacodxa;
...
row4.Fmnnarbg = prevFmnnarbg;
row4.Texseqbg = prevTexseqbg;
}

//The current record becomes, in the next loop, the previous record.
prevEiacodxa = row3.Eiacodxa;
...
//The current row3.Fmnnarbg becomes the previous one
//in the next loop only if it was not altered. That is
if (row3.Texseqbg == 1) {
prevFmnnarbg = row3.Fmnnarbg;
}
//otherwise the new one calculated in the first if statement at the beginning of Part 2 is carried
//to the next loop
prevTexseqbg = row3.Texseqbg; //in the row4 output a sequence greater than 1 tells us that this record came from several records.

count++;

//Part 3
//nothing here
One Star

Re: [resolved] Combining records whose text field continues from one to the next.

Problem solved:

I preceded the tJavaFlex component with the tSortRow component to reverse the record order of the input records (row5). Then changed the code in tJavaFlex to the much simpler:

// start part of your Java code
//initialize variables
String Fmnnarbg = "";
boolean concatenated = false;

// here is the main part of the component,
// a piece of code executed in the row
// loop
// If this is a extension of the next record (i.e. seq > 1)
// then no output, just concatenate the current failure mode text to the previous one
if (row5.Texseqbg > 1) {
Fmnnarbg = row5.Fmnnarbg + Fmnnarbg;
concatenated = true;
continue;
}

//output record if current sequence is 1
//the second condition prevents output when on record 1
if (row5.Texseqbg == 1) {
row6.Eiacodxa = row5.Eiacodxa;
...
row6.Fmncnabg = row5.Fmncnabg;
if (concatenated == true) {
row6.Fmnnarbg = row5.Fmnnarbg + Fmnnarbg;
}
else {
row6.Fmnnarbg = row5.Fmnnarbg;
}
row6.Texseqbg = row5.Texseqbg;
concatenated = false;
row6.rowid = row5.rowid;
Fmnnarbg = "";
}
//Much thanks to Daming Lin for this solution

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

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog