Max Value

[#1]

Good day

I have a data that shows me like that:

A	Date	C
1	01/01/2012	a
1	01/07/2012	b
3	01/03/2012	a
4	01/04/2012	a
4	01/05/2012	b
4	01/06/2012	c

Now i want to create another resident table from this information but with the max date grouping by the column 'A'.

Example, i want this:

A	Date	C
1	01/07/2012	b
3	01/03/2012	a
4	01/06/2012	c

I was trying this in script, but it didn't work:

noconcatenate

Load:

     A,

     Date,

     max(Date) as LastDate,

     C

resident Table 1

Group by A;

Does anyone know how can i get this?

[cesaraccardi]

Hi Jorge,

In your resident load you have the aggregation max(Date) so all the other fields must be in the group by clause and not just the column A. Also keep in mind that if you load the aggregated field max(Date) is useless to load the unaggregated field Date in the same load.

Regards,

Cesar

[#3]

Hello Cesar,

If I put all clauses in group by, example 'Group by A, C;' it will not consider the max date from each value in A, but it will consider the max date for group A and after for group C. This won't be the same information as the last table i posted in my comment.

[cesaraccardi]

Ok, so here is my suggestion:

TMP_TABLE:
LOAD * INLINE
[A,Date,C
1, 01/01/2012, a
    1, 01/07/2012, b
    3, 01/03/2012, a
    4, 01/04/2012, a
    4, 01/05/2012, b
    4, 01/06/2012, c
];
TABLE:
LOAD
          A,
          Date,
          A & '|' & Date as %key;
LOAD
     A          as A,
     Date(Max(Date)) as Date     
RESIDENT TMP_TABLE
GROUP BY
          A;
LEFT JOIN
LOAD
     A & '|' & Date as %key,
     C
RESIDENT
TMP_TABLE;
DROP TABLE TMP_TABLE;
DROP FIELD %key;

[nagaiank · Accepted solution]

The following script also gives the desired result:

TMP_TABLE:

LOAD * INLINE

[A,Date,C

1, 01/01/2012, a

1, 01/07/2012, b

3, 01/03/2012, a

4, 01/04/2012, a

4, 01/05/2012, b

4, 01/06/2012, c

];

Table:

LOAD A

          ,FirstSortedValue(Date,-Date) as LastDate

          ,FirstSortedValue(C,-Date) as LastC

          Resident TMP_TABLE Group By A;

[#6]

Yeah i did the same as you, i used a couple of load resident and group by, but i was wondering if there was other way cause this data base has at about 1.000.000 lines and more than 20 columns, so to do 3 times a load is really hard in this table.

But thanks anyway.

Regards Jorge,

[cesaraccardi]

Nice, I didn't know about this function FirstSortedValue... thats a smarter way to do.

Thank you, krishnamoorthy.

[#8]

Bravo krishnamoorthy

That's what i was looking for.

Many thanks my friend.

Regards,