I am using the previous command, and it is working, however, It only looks at the previous line of data, as I would like it to look at the previous line of data, and if it dose not match the current line, move to the line above the previous line and check if that matches and so on and so forth.
Therefore I need the code to look at all previous lines until it finds a match instead of just looking at the one line above.
I have the code I am using below, and a sample of the data.
The sample shows a table of data with a list of product builders and their Login and Logout times.
TXNDATE with a Sign = 1 is a login time, and TXNDATE with a Sign = 3 is a logout time.
For each Logout time I need to check the previous lines of data to find the correct login time.
The Data is Loaded and sorted by the UnigueID and TXNDATE into a table named PBIII. The code I am using for the LogInTimeCalc is working when the TXNDATE I am looking for is on a previous, otherwise it fails.
The scenario that works for the snapshot is highlighted in green, and the secenario that is not working is highlighted in orange.
I understand why my code is not working as the previous command only looks at the line below, but I am hoping someone might be able to help amend the code so that it looks at all lines above the line I am looking at.
PRIVIOUS (LogInTime) as PriviousLogInTime,
IF ( Sign = 1, Null(),
IF ( UniquieID <> Previous(UniquieID), NULL(),
IF ( TNXDATE > Privious (TNXDATE), Privious (TNXDATE), NULL()))) as LogInTimeCalc