Getting a value of one column by knowing the value of a different column in a different row

[#1]

I am trying to access the time change with respect to a different variable. I cannot find a way to access the time in the time column, with the knowledge of the value in a different column. How is this possible? I have tried everything that I have found on here and on the web and nothing seems to be working.

Thanks for the help.

[vgutkovsky]

There are 2 ways. The best practice method is to use the column name, referenced in square brackets, for example: [Sales This Year]

You can also use the function column(), which references the expression number, as calculated out of the total number of currently-enabled expressions: column(3)

Regards,

Vlad

[#3]

The variable is not in numerical order but I want to find the time, which is in another column, of one number less than the number in that column. So if my chart was as below how would I find the time that has passed from value 11 to value 12?

Time          Value

8:30               1

8:32               11

8:36               21

8:45               2

8:50               12

9:10               22

[vgutkovsky]

Ah, you're talking about row values, not column values. In general, you will probably need to use function above(). In order to tell you for sure though, I'll need to know more about your desired expression. I'm assuming it will be a 3rd column in the chart, right? Will the value of this new expression calculate all the rows above it? So would the value on the Value=12 row calculated 8:50 - 8:30?

Vlad

[#5]

Yes I want to make the time difference a different column. I am trying to get the difference in time between the increase in one value, which is difficult because it is an unknown number of rows above. I am trying to have it search for the row where the value = value -1, then find the time in that row and subtract it from the time in the row where the work is being done.

[vgutkovsky]

Gotcha. This one took me a while to figure out. In theory, you should have been able to use an aggr() to change the sort order. So something like aggr(if(above(total Value)=Value-1,above(total Time)),Value). However, for whatever reason, this wasn't working. Very strange, since I use aggr() like this all the time.

Then, when I started contemplating doing it with a valueloop(), I stepped back off the ledge. Why don't you just take care of this in your script? Just create a 2nd field that would indicate the time minus the time at the previous Value. You can do this easily with ORDER BY and function previous().

Regards,

Vlad

[#7]

How would the script look?

[vgutkovsky]

Something like this, assuming your first table is called Table1:

LOAD

        *,

        if(previous(Value)=Value - 1,

                interval(time#(Time,'hh:mm') - previous(time#(Time,'hh:mm')),'hh:mm')

        ) as IntervalToPrevious

RESIDENT Table1

ORDER BY Value;

Regards,

Vlad