AnsweredAssumed Answered

Compare values in a table based on year

Question asked by aimong on Sep 16, 2014
Latest reply on Sep 18, 2014 by aimong

I have an original table with values like this:
Line ID    Year       Code
0001       2002        300
0001       2003        300
0001       2004        500

I want to find the year the code changes for each line and add that into a table that only shows the year the lines that had code changes. Example:

Line ID     Year_Change
0001         2004

I was thinking of creating 2 dictionary’s {LID: Year} and {LID: Code} and inserting the values into a new table like this:

Line ID      Code2002     Code2003    Code2004
0001           300                300                  500

Then using if statements, such as
If row[1] = row[2]
    Next
Elif: row[2] = row[3]
    next

Else:
    Create dictionary entry {LID: YearChange} which will be inserted into a table.

 

I think I am in a coding rut and need fresh ideas! I’d like to work with the original table. Anyone know of another way to create a loop comparing a previous record based on a 2 variable (LID and Year) expression?

Outcomes