Using ApplyMap() to get largest value (when there are duplicates)

[#1]

Hello,

I'm currently loading two tables.
The first table has some subset of keys.
The second table has a full set of keys with one or more dates associated with it.

I want to create a third table such that it has the subset of keys from table1 with the max(Date).
When I do an ApplyMap() it takes the min(Date) (or perhaps whichever value shows first). Is there a parameter within ApplyMap() I can use to take max(Date)?

Thanks!

Below is an example:

Table1:

A
1
3
5
6
8

Table2:

B	Date
1	1/3
1	5/3
2	6/22
3	9/21
4	2/25
4	7/31
5	2/11
5	5/21
5	6/11
6	6/12
6	11/15
7	7/29
8	2/5
8	9/15
9	12/13

What I want Table3 to look like:

Table3:

A	YAY
1	5/3
3	9/21
5	6/11
6	11/15
8	9/15

And... code that doesn't work (this takes min(Date)):

Table1:
LOAD * INLINE [
A
1
3
5
6
8
];

Table2:
LOAD * INLINE [
B, DATE
1, 1/3/2011
1, 5/23/2011
2, 6/21/2011
3, 9/21/2011
4, 2/25/2011
4, 7/31/2011
5, 2/11/2011
5, 5/21/2011
5, 6/11/2011
6, 6/12/2011
6, 11/12/2011
7, 7/29/2011
8, 2/5/2011
8, 9/15/2011
8, 12/13/2011
9, 1/23/2011
];

MAP_Date:
mapping load
B, DATE
resident Table2;

Table3:
load
A,
ApplyMap('MAP_Date', A, 0) as YAY
resident Table1;

[Clever_Anjos · Accepted solution]

Why don´t you use LEFT JOIN instead?

LEFT JOIN (Table1)
LOAD
B as A,
MAX(DATE) as MAXDATE
RESIDENT Table2
GROUP BY B;

[johnw]

Left join sounds good to me.  But if the map fits your situation better for whatever reason, build your map just like Clever Anjos built the join:

MAP_Date:
MAPPING LOAD
B, MAX(DATE) as MAXDATE
RESIDENT Table2
GROUP BY B;