Use a cell text as a prefix to text in following rows

I need to add prefixes to some rows in a table based on a value in a preceding row to solve some issues problems. The first being that these rows will be transformed into single rows and column headers must be unique, the second is that I need to maintain the context of who is the right person to contact for Marketing or Leasing etc.

Owner: FAR Fund
Developer: FAR Consortium
Physical Address:Port Elizabeth
Year Developed: 1991
Last Refurbished: 2000
Covered Parking Bays: 223
Parking Levels: 2
MANAGED:
Company Name:ABC Property Group (Pty) Ltd
Persons Name: Tony Stark
Designation: Centre Manager
Phone: (+27)00123456
Email: tony.stark@abcpg.com
MARKETING:
Company Name:ABC Property Group (Pty) Ltd
Persons Name:Tony Stark
Designation: Marketing Manager
Phone: (+27)411234568
Email: tony.stark@abcpg.com
LEASING:
Company Name:ABC Property Group (Pty) Ltd
Persons Name: Tony Stark
Designation:Lease Administrator
Phone: (+27)00123456
Email: tony.stark@abcpg.com
MAIN :
Company Name:ABC Property Group (Pty) Ltd
Persons Name: Tony Stark
Designation: Centre Manager
Phone: (+27)00123456
Email: tony.stark@abcpg.com
Owner: FAR2 Fund
Developer: FAR2 Consortium
Physical Address:Port Elizabeth

For example, I would like to be able to add the text “MANAGED” to the 5 rows that follow, and then the. same with “MARKETING”, “LEASING”, and “MAIN” that follow so that the result looks like this:

Owner: FAR Fund
Developer: FAR Consortium
Physical Address:Port Elizabeth
Year Developed: 1991
Last Refurbished: 2000
Covered Parking Bays: 223
Parking Levels: 2
MANAGED:
MANAGED.Company Name:ABC Property Group (Pty) Ltd
MANAGED.Persons Name: Tony Stark
MANAGED.Designation: Centre Manager
MANAGED.Phone: (+27)00123456
MANAGED.Email: tony.stark@abcpg.com
MARKETING:
MARKETING.Company Name:ABC Property Group (Pty) Ltd
MARKETING.Persons Name:Tony Stark
MARKETING.Designation: Marketing Manager
MARKETING.Phone: (+27)411234568
MARKETING.Email: tony.stark@abcpg.com
LEASING:
LEASING.Company Name:ABC Property Group (Pty) Ltd
LEASING.Persons Name: Tony Stark
LEASING.Designation:Lease Administrator
LEASING.Phone: (+27)00123456
LEASING.Email: tony.stark@abcpg.com
MAIN :
MAIN.Company Name:ABC Property Group (Pty) Ltd
MAIN.Persons Name: Tony Stark
MAIN.Designation: Centre Manager
MAIN.Phone: (+27)00123456
MAIN.Email: tony.stark@abcpg.com
Owner: FAR2 Fund
Developer: FAR2 Consortium
Physical Address:Port Elizabeth

Does anyone have a solution?

Thanks

tC/.

Hi @TigerCole,

Doing this with regular nodes turned into something of a marathon, so I’m hoping somebody else can maybe take this and find some shortcuts, or alternative nodes that I haven’t thought of.

Anyway, attached is a workflow that achieves what you have requested. It assumes that the data is correct/consistent (for example that you don’t have a space in “MAIN :”, which you had in your sample data) :wink:

The attached workflow actually has two alternatives. Some people here won’t be surprised to see me include a java snippet (low-code) version, since this task involves deriving information sequentially from a previous row and a regular nodes (no-code) version. I’ve tried to keep the java itself down to the absolute minimum as I think that where possible, non-script nodes are preferable, but as you can see, in this case it greatly reduces complexity (provided you can work out what the java is doing!) compared with my other attempt.

I’m hoping that the comments in the workflow adequately explain what is being done at each stage, but please ask if anything is unclear.

Prefix groups of rows with preceding text.knwf (40.1 KB)

Hoping that this works for you, but also that it will be a catalyst for somebody to find a simpler way! :wink:

Update: I’ve added an additional mechanism drawing on an idea on another post from @Daniel_Weikert, using Moving Aggregation, which has helped a little, but I’m still not feeling like I’ve managed to simplify the rest of the workflow as much as I’d have liked…

Prefix groups of rows with preceding text -v2.knwf (40.1 KB)

1 Like

I also used a tried a bunch of rule engines
don’t think there is a way to do it in 1-3 nodes (beside coding)


I think it works but maybe i missed something. To tired today

1 Like

Hi @Daniel_Weikert , looks good. Can you upload the workflow so I can check your workings… :wink:

apologize,
here it is
ex.knwf (14.4 KB)
br

2 Likes

Thanks @Daniel_Weikert , the only bit that was missing as far as I can see was that the header prefix must only carry down to the subsequent 5 rows. Kudos to you though for the general solution!

Building on your excellent idea though, in terms of pulling down the values down with the Missing Value node (a useful shortcut that I’d completely overlooked), this can be achieved by also pulling down the rowindex for each title and then the rule engine that clears the “prefix” column, can also clear it when it the rowindex is “out of range” from the pulled-down title:

so the final 3 rows don’t have “MAIN:” in front of them…


prefix_text_from_previous.knwf (18.3 KB)

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.