Paste values from one column to other

[Jetro]

Hi!
I have the next table

What I need to find is how I copy the value “Paquetes de maternidad” on another column and fill the rows until “Zacatecas”, then, take the next value “Procedimientos Ginecol…” and do the same, I really try with diferent nodes but I can’t get the solution, must be something like this

Please help and thanks!

[bruno29a]

Hi @Jetro , and welcome to the Knime Community.

You probably need to do some row splitter, and then use the Joiner node or the Cross Joiner node, depending on what you want to do.

Row Splitter

Joiner

Cross Joiner

[Jetro]

Hi @bruno29a!

Thanks for the response, I start to use those nodes, I just can’t find the wey to do it, I mean, how to use the Joiner node to copy the value in other column and stop at some especific value?

I really appreciate your help.

[badger101]

Hi @jetro, if the image below shows the results you wish for, then this workflow here
(Paste values solution.knwf (51.2 KB)) is the possible solution you seek.

[mlauber71]

@Jetro your solution might look something like this. Maybe you provide us with the sample data. It will be a combination of using row_ids to identify the ‘markers’ jon them back into the starting place and then sort by the row_id and then fill in the blank lines with the values (with the help of the Missing value node - using the last value if missing).

Reference Column Filter Node with LIKE operator

@Brotfahrer I had another look at the data and I would like to point you to another systematic approach. Using the underlying structure of the files to mark and split the relevant data. the big blocks seem to be defined by parts marked ECU. With the help of row numbers, you can identify and mark these blocks as “Level1” value pair. You would have to deal with the header values separately and maybe add the file name as constant column. The new LevelB is formed by Blocks that can be identified b…

[badger101]

If the solution I provided earlier looks messy, you may combine nodes together like this.

Here’s the new tidier workflow:
Paste values solution.knwf (49.2 KB)

[gonhaddock]

Hi @Jetro
Please find an alternative workflow solution.

20220425_unnesting_column.knwf (49.7 KB)

BR

[Jetro]

Hi @badger101 !
I think I’m close to the solution with your help, It’s my first time in the forum and It’s amazing this community.
I left you my data, I try with your solution but I don’t get the final table.
First knime work.xlsx (23.0 KB)

I really appreciate your help

[Jetro]

I don’t understand why only in the Reference Row Filter Node only work with CDMX and no with the other words, any clue?

[badger101]

Hi @jetro I just logged in. Give me a few minutes to look at your data.

[badger101]

Hi again @Jetro it appears that your first column of the raw data contains spaces after the last word. This requires a minor tweak to clean your data.

Paste values solution.knwf (55.0 KB)

Here’s the updated workflow to strip all the unwanted spaces. It’s performed by the String Manipulation Node before the Missing Value Node. In the future, if you need other type of manipulation for cleaning up the raw data, you just add the new relevant nodes before the Missing Value Node like I did here.

[Jetro]

Thanks a lot @badger101 , that final workflow help me to solve it!

[badger101]

Glad to help you out!