Getting next higher value in a table

I have a table with meter_readings and dates for various cars in various organizations

Org, Vehicle, Reading, MeterDate
22, 11003-01, 10, 6/01/2020
22, 11003-01, 60, 11/01/2020
22, 11993-01, 500, 16/01/2020
22, 11993-01, 550, 21/01/2020
22, 11993-01, 600, 26/01/2020
23, 902-01, 1000, 17/01/2020
23, 902-01, 1050, 22/01/2020
23, 902-01, 1100, 27/01/2020

I wish to do computations so I could compare the values with the next available row for that car.

Org, Vehicle, Reading MeterDate, NextReading, NextDate
22, 11003-01, 10 6/01/2020, 60 11/01/2020
22, 11003-01, 60 11/01/2020,
22, 11993-01, 500 16/01/2020, 550 21/01/2020
22, 11993-01, 550 21/01/2020, 600 26/01/2020
22, 11993-01, 600 26/01/2020,
23, 902-01, 1000, 17/01/2020, 1050 22/01/2020
23, 902-01, 1050, 22/01/2020, 1100 27/01/2020
23, 902-01, 1100, 27/01/2020,

What combination of nodes do I use?
Have you done something similar?
Are there examples available that I could check out?

DB Connector --> DB Query Reader --> Math Formula to compute RowId+1
–> DB Query Reader --> Math Formula to compute RowId
Then a join where
OrgId = OrgId
AssetNumber = AssetNumber
RowId+1 = RowId

Gave me the result!Getting Next Higher Value

3 Likes

Try this (it runs even without database)
KNIME_forum_22975.knwf (22.9 KB)

1 Like

Saw that.
That’s beautiful.
Its clever how you grouped it, re-organized it and then un-grouped it.
Thanks for taking the effort.

I am required to compute utilization summary.
Imagine there is a car.
The meter readings are recorded regularly.
Usually once every week.
There could be occasions where meter reading of one or two consecutive months may not be available.
Based on that, I am required to compute the monthly kms it is travelling.
At any point of time, I am required to produce monthly kms for the last 18 months.

For now, I have been able to identify utilization if records are available for every month.
Next, I need to come up with a way when records are not available for consecutive months.
I need a way to spread the average monthly value over multiple columns.

I am getting used to Knime slowly. I can see the potential. As long as I am able to progress I’m good.

UtilizationSummary.xlsx (361.1 KB)

004_Asset_Utilization (3).knwf (21.1 KB)

I modified your worflow appending a new way to process data.
I restored column5 value (only month, without day, to create a dae programmatically).
Next I rewrite the second part of workflow to heck difference between start and end month, duplicate row n-times (by loops) and I used iteration value to append n-month to start month.
Here is workflow.
If you want I can explain all parts!
Bye!

004_Asset_Utilization_pigreco.knwf (57.1 KB)

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.