3 Replies Latest reply: Sep 25, 2012 11:26 AM by rkapadia16

# IF statement to deal with duplicates within fields

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.

• ###### Re: IF statement to deal with duplicates within fields

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)

*,

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

Resident TT;

HtH

Roland

• ###### Re: IF statement to deal with duplicates within fields

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?

• ###### Re: IF statement to deal with duplicates within fields

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