IF statement to deal with duplicates within fields

[#1]

Hi,

Is it possible to do calculations based on duplicates within the same field.

For example:

Date	Time	ID	Desc
11/01/2012	12:20 PM	1100007	Alloc
11/01/2012	12:17 PM	1100007	Alloc

What I need to do is, if the IDs are the same (i.e. 1100007 = 1100007) and the Desc's are the same (i.e. Alloc = Alloc), then work out the absolute difference of the "Time" field (which is 3 minutes in this example).

I'm not sure what syntax to use which will enable me to do this.

Any help would be greatly appreciated.

Thanks

Raj.

[#2]

Hi Raj,

I assume that you load the table in correct order (ID, Desc, Date) and you are having always a pair of rows, if they are equal in (ID, Desc, Date). With this requirements try to use this:

TT:

// Table is loaded in sort order

Join(TT)

LOAD

    *,

    if(Previous(ID) = ID and Previous(Date) = Date and Previous(Desc) = Desc, Value+Previous(Value), 0) as SumVal

Resident TT;

HtH

Roland

[#3]

Try using the "ABOVE" statement in order to check the previous row  of "ID" and "Desc"

for comparing with current row and based on this condition find the difference

[#4]

Thanks for that, I'll give that a try now.

Is there a way the above can be used if you have more than two duplicate IDs?  If yes, then is it possible to get the difference between max and min times?