Read previous row based on value of next row

Hi all,

I am trying to extract data using string manipulation.
Although there is a row that doesn't consist of any fix keyword but there is a fix keyword in the next row.
How do I get this done using Utility-Collection Manipulation or is there any other alternate?
 
The data is row wise. Its confidential so I can give you the rough idea. Below the data is in collection rows and I've changes the values.

Row1 - Keyword1
Row2 - Country Name
Row3 - Country Name
Row4 - 999,999.99 NAT BK City Name T/D 2.330 FEB 27 19 99,999,999.99 100.000 99,999,999.99 0.00 1.999
Row5 - EQUITY(Keyword2)

In this case, I've to read 1.999 that's highlighted in red based on the keyword equity in next row. Initially I am discarding all rows that aren't containing the keyword like Keyword1 or Keyword2 (Equity).
I want to save this row from being discarded before Keyword Equity.
 

sivagelli

Well-Known Member
You can use a Loop stage to get the row index of the matching row by using a Counter. Once you have the Row index, get the required row using 'Copy Rows' action passing the 'Counter-1' from Internal Collections VBO. You now have a collection with Row 4, on which you can use the String Manipulations to get the value 1.999.

This is lengthy process, otherwise you can write a code stage.
 
You can use a Loop stage to get the row index of the matching row by using a Counter. Once you have the Row index, get the required row using 'Copy Rows' action passing the 'Counter-1' from Internal Collections VBO. You now have a collection with Row 4, on which you can use the String Manipulations to get the value 1.999.

This is lengthy process, otherwise you can write a code stage.

Thanks Sivagelli. Your solution seems good but I figured out an alternate solution.
Using choice stage and in the "otherwise", i am writing data to a single row collection. Now if the next row contains keyword Equity, it adds a row to my final collection and copies the data from single row to collection to it. And for rest of the data I am already using keywords, so I am getting them already and using this solution, all the required data is in order too.
 
Top