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

Mon, 13 Jun 2011 17:50:32 GMT

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;

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

Clever_Anjos — Mon, 13 Jun 2011 19:03:35 GMT

Why don´t you use LEFT JOIN instead?

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

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

johnw — Mon, 13 Jun 2011 19:09:29 GMT

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;

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

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

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

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