topic Re: IF statement to deal with duplicates within fields in QlikView

IF statement to deal with duplicates within fields

Tue, 25 Sep 2012 12:39:47 GMT

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

Tue, 25 Sep 2012 14:43:13 GMT

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

Re: IF statement to deal with duplicates within fields

Tue, 25 Sep 2012 14:43:51 GMT

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

Re: IF statement to deal with duplicates within fields

Tue, 25 Sep 2012 14:58:33 GMT

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?