Firstsortedvalue for Datetime

[#1]

Hi All,

I have the below data set and I want only the rows with latest execution datatime through an expression. can you please suggest how to get the desired output.

Original Data Set

Job	Task	Execution_Datetime	Execution Count
1	ABC	12/21/2016 10:00	100
1	ABC	12/21/2016 9:30	20
1	ABC	12/20/2016 8:31	10
1	ABC	12/20/2016 7:30	5
2	BBB	12/21/2006 10:01	50
2	BBB	12/21/2006 9:30	20
2	BBB	12/20/2006 11:01	8
2	BBB	12/20/2006 8:30	10

Desired output

Job	Task	Execution Count
1	ABC	100
2	BBB	50

[Chanty4u]

Not Tested. But try this

aa:

LOAD * INLINE [

    Job, Execution_Datetime, ExecutionCount

    1, ABC, 12/21/2016 10:00, 100

    1, ABC, 12/21/2016 9:30, 20

    1, ABC, 12/20/2016 8:31, 10

    1, ABC, 12/20/2016 7:30, 5

    2, BBB, 12/21/2006 10:01, 50

    2, BBB, 12/21/2006 9:30, 20

    2, BBB, 12/20/2006 11:01, 8

    2, BBB, 12/20/2006 8:30, 10

]

;

LOAD *,

//max(Date(Date#(Execution_Datetime,'DD/MM/YYYY hh:mm')))

Timestamp(max(Execution_Datetime)) as TimestampField

  Resident aa;

[lironbaram]

assuming you use a table

with job and task as dimensions

you can use this expression

firstsortedvalue(ExecutionCount,-Execution_Datetime)

[#4]

I tried and it did not work. Here is the script.

aa:

LOAD * INLINE [

    Job, Task,Execution_Datetime, ExecutionCount

    1, ABC, 12/21/2016 10:00, 100

    1, ABC, 12/21/2016 9:30, 20

    1, ABC, 12/20/2016 8:31, 10

    1, ABC, 12/20/2016 7:30, 5

    2, BBB, 12/21/2006 10:01, 50

    2, BBB, 12/21/2006 9:30, 20

    2, BBB, 12/20/2006 11:01, 8

    2, BBB, 12/20/2006 8:30, 10

];

LOAD *,

//max(Date(Date#(Execution_Datetime,'DD/MM/YYYY hh:mm')))

date(date#(left(Execution_Datetime,10),'MM/DD/YYYY'),'MM/DD/YYYY') as ExecutionDateTime

//Timestamp(max(Execution_Datetime)) as TimestampField

Resident aa;

Tried on straight table with the below 2 expressions

Job and Task as Dimension and below 2 as expressions.

firstsortedvalue(ExecutionCount,-Execution_Datetime)

firstsortedvalue(ExecutionCount,-ExecutionDateTime)