How to copy first row record in next row until next row have value

[ladikrishna]

HI experts,

I have one table  like below 

LOAN_NUM	BAL	DATE	ADV.AMOUNT	outstanding
123	1250	01-01-1900	20
			21
			34
			45	25
345	1500	01-01-1901	23
			25
			27
			28	27

I want only loan_num to be filled in next rows unless next rows have  loan_num. Below will be final output.

LOAN_NUM	BAL	DATE	ADV.AMOUNT	outstanding
123	1250	01-01-1900	20
123			21
123			34
123			45	25
345	1500	01-01-1901	23
345			25
345			27
345			28	27

[koryknick · Accepted solution]

@ladikrishna - SnapLogic is a streaming platform and doesn't hold context of previous or next input documents - each snap works only on the current input document.  To perform the type of contextual processing you need here, we will employ the Script snap which allows us to write more complex Javascript (or Python/Ruby) code.

Attached is a sample pipeline that should do what you are looking for.  Please download and unzip the attachment, the import the SLP as a new pipeline.  The Script snap is what performs the data value replication across subsequent records until a new value is found.

I added only a handful of lines to accomplish the desired result:

Line 33 -  declare a new variable to hold the previous loan number value and initialize to an empty string

var prevLoanNum = "";

 Line 40 - create the outDoc variable as a copy of the input document 

var outDoc = new LinkedHashMap(inDoc);

Lines 42-46 - check if the current record doesn't have loan num and use prevLoanNum, or if it has a new value then assign it to prevLoanNum

if ( outDoc.LOAN_NUM == "" ) {
	outDoc.LOAN_NUM = prevLoanNum;
} else {
	prevLoanNum = outDoc.LOAN_NUM;
}

Line 48 - output the new outDoc value

Hope this helps!

[ladikrishna]

Hi @koryknick ,

Thanks for the solution, attached snap is working fine .