Lag Column with Negative value?

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.)

Cheers, gabor

We have this on our list, I just increased the priority :)

However, Gabors solution is great :)

4 Likes

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

Hi Iris,
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?

Andrea

6 Likes

Hi there!

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 :wink:

Workflow is attached so check it out and if any questions feel free to ask.
2019_04_03_ReverseLagColumnOption.knwf (16.2 KB)

Regards,
Ivan

15 Likes

Hi,

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?

Cheers
Isabell

1 Like

Hi Isabell!

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 :wink:

Br,
Ivan

2 Likes

HI @ipazin,

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 :wink:

PS: The following works too:

  1. Extract Row index via Rule Engine “TRUE => $$ROWINDEX$$”
  2. Sort the index descending
  3. Apply Column Lag
  4. Sort the index ascending
  5. Remove row index column

Kind regards
Mike

4 Likes

Hi there @mw,

there is always a chance :slight_smile: For now all I can do is give +1 to the ticket.

Br,
Ivan

6 Likes

Hello,
+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…
BR Christoph

3 Likes

Hello @CEbersbach,

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 :wink:

Br,
Ivan

5 Likes

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…

1 Like

Hi @iCFO -

+1 added!

2 Likes

Hi guys,
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.

5 Likes

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.

Hi @RiznyLafi

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 :wink:

Best wishes,

Ael

2 Likes

Dear ScottF,
Regret to inform that typing in +1 as Lag value generates exactly the same as typing in 1 (which means lag(-1))
Regards,
Henk

I typed in +1

and this is what Knime configured

1 Like

Seems like this high-demand request for a new Lag Node is still not implemented. But luckily the solution by @ipazin with the Moving Aggregation puts me out of misery. Thank you!

1 Like

I am in team “moving aggregation” however the simple logic from @HansS by changing the sort order, do a normal lag and sort again is also nice (primarily because I myself only thought about moving aggregation from the beginning)
I think what most of us like about KNIME is the flexibility to solve a problem.
br

2 Likes