Aggregate in Script

[#1]

Hi,

I have a scenario with data like below;

CounterID	Date
1	04/10/2014 22:38:26
1	04/10/2014 22:39:26
1	04/10/2014 22:51:09
2	06/10/2014 13:56:51
3	04/10/2014 22:38:26
3	04/10/2014 22:39:26
3	04/10/2014 22:45:00

And I need to fetch the Latest Date for each CounterID in the SCRIPT and not in the expression.

so that I get a field with  Latest Dates for each ID;

CounterID	Date
1	04/10/2014 22:51:09
2	06/10/2014 13:56:51
3	04/10/2014 22:45:00

I tried using Peek grouping by CounterID, but not getting the desired Result.

Appreciate any

Help on the same !

[saumyashah90]

File1:

Load

CounterID,

Date(Subfield(Date,' ',1),'DD/MM/YYYY') as Dt

from xyz .

File2:

Load

CounterID,

Max(Dt)

resident File1 group by CounterID;

//If not needed

Drop table File1;

[#3]

firstsortedvalue(counterid,-date)   - u ll get max values

[MK_QSL]

T1:

Load

  CounterID,

  Timestamp(Date) as Date

Inline

[

  CounterID, Date

  1, 04/10/2014 22:38:26

  1, 04/10/2014 22:39:26

  1, 04/10/2014 22:51:09

  2, 06/10/2014 13:56:51

  3, 04/10/2014 22:38:26

  3, 04/10/2014 22:39:26

  3, 04/10/2014 22:45:00

];

Left Join

Load

  CounterID,

  FirstSortedValue(Date,-Date) as MaxDate

Resident T1

Group By CounterID;

[MarcoWedel]

table2:
LOAD
  CounterID,
  Timestamp(Max(Date)) as MaxDate
Resident table1
Group By CounterID;

[its_anandrjs]

Hi,

There is another way of doing the same without change the timestamp format

Tab1:
Load
CounterID,
Timestamp#(Date) as Date
Inline
[ CounterID, Date
1, 04/10/2014 22:38:26
1, 04/10/2014 22:39:26
1, 04/10/2014 22:51:09
2, 06/10/2014 13:56:51
3, 04/10/2014 22:38:26
3, 04/10/2014 22:39:26
3, 04/10/2014 22:45:00
];

Tab2:
Load
CounterID,
Date#(MaxString(Date),'DD/MM/YYYY hh:mm:ss') as MaxDateField
Resident Tab1
Group By CounterID; 

Regards

Anand

[MarcoWedel]

Hi,

one solution might be:

tabDates:
LOAD * INLINE [
    CounterID, Date
    1, 04/10/2014 22:38:26
    1, 04/10/2014 22:39:26
    1, 04/10/2014 22:51:09
    2, 06/10/2014 13:56:51
    3, 04/10/2014 22:38:26
    3, 04/10/2014 22:39:26
    3, 04/10/2014 22:45:00
];

tabLatestDates:
LOAD CounterID,
    Timestamp(Max(Date)) as MaxDate 
Resident tabDates 
Group By CounterID;

[rwunderlich]

I would guess that using MaxString() would be significantly slower than the max() or FirstSortedValue() suggestions given earlier.

-Rob