7 Replies Latest reply on Oct 6, 2017 3:19 PM by mike.shapiro.0

# If ID has two row records, grab one with closed [date] to [date2]?

I'm looking to build a calculation that excludes multiple rows of records for the same [ID] by only using the row with the [Date] closes to [date2]

Any help with be appreciated.

• ###### 1. Re: If ID has two row records, grab one with closed [date] to [date2]?

the DATEDIFF function tells you the difference between two dates.

You can find all the date functions here:

So you can do a FIXED LOD on ID to get the min datediff.  It would looks something like this:

{  FIXED [ID]  : MIN(  ABS(  DATEDIFF ( 'days', [Date], [Date2]) ) )  }

That will tell you the smallest difference among the rows for each ID.

Then you can filter on that.

IF ABS(  DATEDIFF ( 'days', [Date], [Date2]) ) = min( [ that calc] ) then that's the row you want to use.

• ###### 2. Re: If ID has two row records, grab one with closed [date] to [date2]?

Hi Mike,

Find my approach as reference below and stored in attached workbook version 10.2 located in the original thread

0. Used dataset

1. Define parameter

2. Define calculated field Difference: abs(DATEDIFF('day',[date],[Parameter 1]))

3. Define calculated field Display: ({fixed [Id]:min([difference])})=([difference])

4. Drag Display on Filter and set TRUE

5. Drag required objects to the indicated locations and show parameter control

Regards,

Norbert

• ###### 3. Re: If ID has two row records, grab one with closed [date] to [date2]?

Hi Joe,

Way to go;)

Regards,

Norbert

• ###### 4. Re: If ID has two row records, grab one with closed [date] to [date2]?

Hi Joe,

That is throwing me some errors.  My calc 2 shows red on the '=' part and throws me an aggregation error.

Calc1:

{  FIXED [ID]  : MIN(  ABS(  DATEDIFF ( 'day', [Review Date], [Draft Date]) ) )  }

Calc 2:

IF ABS(  DATEDIFF ( 'day', [Review Date], [Draft Date]) ) = min([Calc1])
then 'yes' else 'no'

END

• ###### 5. Re: If ID has two row records, grab one with closed [date] to [date2]?

Try this:

IF ATTR( ABS(  DATEDIFF ( 'day', [Review Date], [Draft Date]) ) )  = min([Calc1])

Or maybe just take the MIN() off [Calc1].  Maybe we don't need that there.

• ###### 6. Re: If ID has two row records, grab one with closed [date] to [date2]?

thanks Joe!  Removing the MIN() off [Calc1] was the way to go.

• ###### 7. Re: If ID has two row records, grab one with closed [date] to [date2]?

Update here.  The calculations show as correct, however the query takes a extremely long time to run, it if ever will finish.

Anyone know of a way to speed up a calc like this?   This is running at record level on several hundred thousand rows.