LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tobbe

Hi,

I'm want to read a file with meter readings. They are sorted by Meter and Time. The meter value is accumulated and I want to calculate the usage between each meter reading.

The file looks like this:


Meter Time Reading
7876561 2012-02-27 00:00:00 8846.71
7876561 2012-02-27 01:00:00 8846.77
7876561 2012-02-27 02:00:00 8846.84
7876562 2012-02-27 00:00:00 7465.23
7876562 2012-02-27 01:00:00 7893.30
7876562 2012-02-27 02:00:00 8210.67


One file could consist of 1-2 million rows.

Is there a way to store a value from one row to actually use it on the next row?

I actually got a solution where I make a copy of the file with an offset for the reading with one row and then join them but it doesn't feel like a safe solution.

Could anyone point me in the right direction?

Best Regards Torbj�rn

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

It's easy. Make sure your data is sorted by Meter,Time then add an AGG node

In the AGG node, Group By Meter,Time (same as in the sort)

if firstInGroup then p_Reading=0.0
delta = Reading - p_Reading
emit *
emit Prior=p_Reading
emit delta
where lastInGroup
p_Reading=Reading


Note that the last line here MUST be after you "use" the value in lines 2 and 4.
I also tend to put it at the very bottom, after everything else.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tobbe

Thanks for your quick response!

I couldn't get your code to generate expected values but you sure pointed me in the right direction.

My code:

# Initialize variables
if firstExec then
{
p_reading = 0.0
p_meter = "-"
}

# If we haven't changed meter
if (meter==p_meter) then
delta = reading - p_reading
else
delta = 0.0

emit *
emit p_reading as prior_reading
emit p_meter as prior_meter
emit delta
where lastInGroup

# Store values for next record
p_reading=reading
p_meter=meter

And this generated this output:

meter time reading prior_reading prior_meter delta
7876561 2012-02-27 00:00:00 8846.71 0.0 - 0.0
7876561 2012-02-27 01:00:00 8846.77 8846.71 7876561 0.0600000000013
7876561 2012-02-27 02:00:00 8846.84 8846.77 7876561 0.0699999999997
7876562 2012-02-27 00:00:00 7465.23 8846.84 7876561 0.0
7876562 2012-02-27 01:00:00 7893.3 7465.23 7876562 428.07
7876562 2012-02-27 02:00:00 8210.67 7893.3 7876562 317.37

Thanks for your help. Lavastorm is pretty cool!

/ Torbj�rn