I would like to reference the next (future) row, similar to how the Lag Column can reference the previous row. This way I can measure if the next row will be different. That way I can create something like this:
Mon Name Next Last Mon
Jan John John
Feb John Mike Feb
Jan Mike Mike
Feb Mike Mike
Essentially I would use a Java Snippet to see if Name.equals(Next), if not then that's the last month this rep is on record.
Ideally, an improvement to the Lag column is needed.
But to do the Lag Column trick in the opposite direction is possible.
Use a Maths node with expression RowIndex.
Then sort in Descending order on RowIndex with Sorter node.
Then use the Lag Column again.
Isn't it easier/faster to use the Lag Column and exchange the role (name/position also if you prefer) of the columns in further processing? (Maybe I misunderstand the requirements.)
We have this on our list, I just increased the priority :)
However, Gabors solution is great :)
I think Richard's approach is the way to go.. reverse the table, lag it, then reverse it back... But yes, it would be lovely to just have this built in.
Too bad Java Snippet can't access a row other than the one it's currently in
after 4 years, do you have implemented this features?
It would be very useful for me… I cannot sort desc-lag-sort asc a table of 500k elements every single time the workflow is executed: it is so sloooooooow.
The Gabors solution is not useful in my case.
Using the Moving Aggregation can I lag a column in the opposite way? How?
You are right! Using Moving Aggregation you can simulate negative lag interval in Lag Column node. Window type is Forward, window length-1 determines the lag interval and aggregation is Last on lagging column.
Additionally there is another way using just a bit of filtering where number of excluded rows determines the lag interval
Workflow is attached so check it out and if any questions feel free to ask.
2019_04_03_ReverseLagColumnOption.knwf (16.2 KB)
same as Andrea, I would also be interested in the -1 Lag Column feature as this also allows lagging RowIDs (which is unfortunately not possible for the solution as proposed by Ivan (ipazin)). Is there any update on this feature?
This haven’t been implemented. Your and Andrea requests have been noted.
Regarding lagging Row IDs use RowID node to get Row ID values in column and you can use above solutions
any chance to see this in a future update or will the can being kicked down the road for eternity. No complain, there certainly are bigger things on the to do list. Though, there will always be something more important, won’t it
PS: The following works too:
- Extract Row index via Rule Engine “TRUE => $$ROWINDEX$$”
- Sort the index descending
- Apply Column Lag
- Sort the index ascending
- Remove row index column
Hi there @mw,
there is always a chance For now all I can do is give +1 to the ticket.
+1 from my side. Coming from Alteryx and having workflows with 20+ MultiRow Formulas with complex formulas (math, string, … reaching out to +1/-1/+2/-2 columns within one expression - partly grouped on certain fields) would definitely make my life a bit easier. Even though I am afraid those solutions will still be messy/have to be put to Metanodes…
have added +1 for you. (Internal reference: AP-4515)
Regarding the missing (formulas) functionalities from other tools (Alteryx) I suggest you open up a topic for it. Have seen quite a bit Alteryx users so you might get proper help/suggestions from them if needed
Add another +1 to the MultiRow Formula request for me please! I am also juggling a ton of lag columns and struggling to recreate MultiColumn+MultiRow Alteryx Formulas in KNIME…
This is my first post on Knime forum. I created a component which does Lead(Negative Lag) of a column.
I have shared it via Knimehub . Knime hub is new to me , If the component does not work notify me.
Doesn’t work for me. The label dropdown yeilds an empty list…
This component like Lag only list down Numerical variable. Is the dropdown empty even with numerical columns?
Can you just share the data set column types.
Thanks for posting a component to generate negative lag. It is true that the -Lag Column- node lacks of this possibility and only generates positive lacks.
Alternatively and complementary to your solution, one can use the -Moving Aggregation- node for negative lag generation which is a much more powerful node than the -Lag Column- node for data lagging and convolution, allowing positive and negative lags as well
Hope it helps too
Regret to inform that typing in +1 as Lag value generates exactly the same as typing in 1 (which means lag(-1))
I typed in +1
and this is what Knime configured