Lag Column with Negative value?

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      

etc.

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.

3 Likes

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.

 

Simon.

2 Likes

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

4 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

6 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

3 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

5 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

1 Like

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

3 Likes