Clean and join CSV with separate header and detail rows

Hi, I have data in the following format.

  • Header rows contain Company ID (column A) and Transaction ID (column B)
  • Detail rows contain Line IDs (column C) and Amounts (column D)
  • Company ID and Transaction ID are blank for detail rows

See screenshot of input:
image

Desired output:
image

(Ignore the different values in column D as I simulated the amounts with random values.)

Any recommendations on what nodes I can use to join the details from header rows (columns A and B) with the detail rows (columns C and D)?

Right now I am using Excel VBA but this would be difficult for other users who don’t understand VBA.

Hi there!

First use Column Splitter node to split A and B columns from C and D. Then on A and B column data set use Missing Value node with Previous Row for missing value. Then use Joiner node to join on RowID two data sets. At the end use Missing Value node again with Remove Row for missing value. That should be it :wink:

Here is a workflow:
Join_Header_and_Detail.knwf (7.6 KB)

Br,
Ivan

2 Likes