topic Firstsortedvalue for Datetime in QlikView

Firstsortedvalue for Datetime

Thu, 22 Dec 2016 06:07:59 GMT

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

Re: Firstsortedvalue for Datetime

Chanty4u — Thu, 22 Dec 2016 07:25:28 GMT

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;

Re: Firstsortedvalue for Datetime

lironbaram — Thu, 22 Dec 2016 07:37:13 GMT

assuming you use a table

with job and task as dimensions

you can use this expression

firstsortedvalue(ExecutionCount,-Execution_Datetime)

Re: Firstsortedvalue for Datetime

Thu, 22 Dec 2016 08:13:07 GMT

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)