LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rbonneux

Hello, i'm trying to extract the First and last occurance of multiple dates in a list

TRACE A_DT B_DT C_DT
1 2011-06-24 2011-06-24 2011-02-14
1 2011-06-14 2011-06-15 2011-06-01
1 2011-06-02 2011-06-03 2011-05-19
1 2011-06-06 2011-06-07 2011-05-20
1 2011-06-14 2011-06-15 2011-06-01
1 2011-06-02 2011-06-03 2011-05-19

So I first create Sort node and Sort by TRACE
I then create an Aggregate NODE with the following Code

ORDERS_TOTAL = count()
FIRST_A_DT= min(A_DT)
LAST_A_DT= max(A_DT)
FIRST_B_DT= min(B_DT)
LAST_B_DT= max(B_DT)
FIRST_C_DT= min(C_DT)
LAST_C_DT= max(C_DT)

emit TRACE, ORDERS_TOTAL
FIRST_A_DT,LAST_A_DT,
FIRST_B_DT,LAST_B_DT,
FIRST_C_DT,LAST_C_DT
where LastInGroup

GROUP_BY = TRACE

which outputs
TRACE FIRST_A_DT LAST_A_DT ...
1 2011-06-02 2011-06-02 ...

When what i Really Want is
TRACE FIRST_A_DT LAST_A_DT ...
1 2011-06-02 2011-06-24 ...

Any ideas why this is happening. I do not want to create 2 plus nodes to isolate the first and last date as i have much more than listed. (just this example would require at least 6 + the xref or join...)

Thanks Ryan

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Try this:

if firstInGroup then {
FIRST_A_DT= date(2099,12,31)
LAST_A_DT= date(1970,1,1)
FIRST_B_DT= date(2099,12,31)
LAST_B_DT= date(1970,1,1)
FIRST_C_DT= date(2099,12,31)
LAST_C_DT= date(1970,1,1)
}

FIRST_A_DT= min(FIRST_A_DT,A_DT)
LAST_A_DT= max(LAST_A_DT,A_DT)
FIRST_B_DT= min(FIRST_B_DT,B_DT)
LAST_B_DT= max(LAST_B_DT,B_DT)
FIRST_C_DT= min(FIRST_C_DT,C_DT)
LAST_C_DT= max(LAST_C_DT,C_DT)

emit TRACE, ORDERS_TOTAL
FIRST_A_DT,LAST_A_DT,
FIRST_B_DT,LAST_B_DT,
FIRST_C_DT,LAST_C_DT
where LastInGroup

GROUP_BY = TRACE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

Ryan,

"min" and "max" operate by comparing a list of items (see your BRAINscript help). In the way you have it written now, they are only comparing the current value of the X_DT field. So essentially you are saying

X = min(date). Well, the answer to that can only be X = date because there is nothing else to look at.

So your min/max's are always going to reassign your FIRST/LAST variables to the current record, every time. What your getting reported is just the last records date value 2011-06-02.

What you want to do is declare a

"Where firstInGroup" section, and setup an initial value for all your FIRST/LAST variables. Then in your aggregation statements use

FIRST_A_DT= min(A_DT, FIRST_A_DT)

That should assign FIRST_ the value of whichever is lower: the current value of A_DT or the previous iteration value of FIRST_.

Regards,
Timon Koufopoulos
MDA Support.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: maria.pislopez

Hello Ryan,

If you replace min and max with the functions groupMin and groupMax, the min and max for each value of trace will be outputted.

Here is an example:

ORDERS_TOTAL = count()
FIRST_A_DT= groupMin(A_DT)
LAST_A_DT= groupMax(A_DT)


Good Luck!
Maria