Repeating row previous value (NOT COLUMN)

[jamalbutt1232]

Hi, I cannot find any node that can help me in repeating the previous row value. For example:

I want this output:

I have used the MISSING value node but to repeat the previous value but it works on a column, not on a row? Is there any way of doing so?

[knightyboy]

For an easy fix as long as the input table remains the same, when you load it into KNIME, don’t promote any of the rows into headers, so you’re left with ‘column1’ then ‘column2’ etc.

Create a separate table, and input the values that you need

e.g.

You can then concatenate the 2 together and filter out where ‘column3’ is missing

So it’ll end up like this

[jamalbutt1232]

This will only help if months are static in number. I need something where I don’t have to specifically create a table and concatenate it. I need to do something dynamic here. Any lead on it?

[ipazin]

Hello @jamalbutt1232,

but you are showing screenshots from Excel. The question is how data is organized inside KNIME? Are those values column headers or not? Can you share input sample file?

Br,
Ivan

[knightyboy]

Ok

1. Transpose your excel file
2. Tick the ‘Replace RowID’ and nothing else selected
3. Tick ‘Create new column’ and name it ‘Rows’ and nothing else selected
4. Column filter to leave Row0 only
5. Create table

5. Concatenate data
6. Repeat step 2
7. Repeat step 3
8. Left Outer Join on column ‘Rows’
9. Create rule

10. Column filter removing ‘Rows’ and ‘Row0 (#1)’
11. Repeat step 2
12. Transpose

And your output will be this

And it would change if the months changed, you wouldn’t need to do anything else

You can obviously add a column rename node at the end to rename them if you please, but I guess you wouldn’t need to if you then output to Excel not including the column names

[knightyboy]

Trying to see if I can upload the file for you Repeat row.knwf (26.6 KB)

[Daniel_Weikert]

You could unpivot using fill missing value node and pivot again I guess

[jamalbutt1232]

Thankyou it worked. But I have found another interesting way of doing it which is pretty simple. As in my case the specific row is always the first one. So just transpose it, pick the first column which will reflect the first row. Use the MISSING row node and implement previous value logic. Then transpose the row it will do the trick. It did for me. Thank you though for putting such effort!

[jamalbutt1232]

Unpivoting it will be much more effort. As its the first row not the header. So we have to always make it a header, it cant be directly converted into header as their are missing value which is the problem. As header cannot be null. It is doable but much more effort

[knightyboy]

Oh wow, I didn’t even know about that feature. Well that’s is farrrrrrrr simpler, haha.