topic Re: select the latest date data in QlikView

select the latest date data

userid128223 — Wed, 31 May 2017 05:03:28 GMT

I have data in following format:

Date	Class	Grade	Teacher	Notes
2017-05-29 10:25:32:234	Math	6	Ms Walsh	dafd
2017-05-20 9:06:42:345	Science	10	Ms Helen	dafdaf
2017-05-20 23:33:46:453	Chemistry	6	Mr Tom	dfdfd

How do you pick the record with latest date & time

Result:

Date	Class	Grade	Teacher	Notes
2017-05-20 23:33:46:453	Chemistry	6	Mr Tom	dfdfd

Re: select the latest date data

tresB — Wed, 31 May 2017 05:06:51 GMT

Try like:

Date : Timestamp(Max(Date))

Class : FirstSortedValue(Class, -Date)

Garde: FirstSortedValue(Grade, -Date)

Teacher: FirstSortedValue(Teacher, -Date)

Notes: FirstSortedValue(Notes, -Date)

Re: select the latest date data

userid128223 — Wed, 31 May 2017 05:11:25 GMT

Thanks for response

I want this to be done at script level.

Re: select the latest date data

tresB — Wed, 31 May 2017 05:18:32 GMT

This works in script too.

Load

Timestamp(Max(Date)) as Date,

FirstSortedValue(Class, -Date) as Class,

FirstSortedValue(Grade, -Date) as Grade,

FirstSortedValue(Teacher, -Date) as Teacher,

FirstSortedValue(Notes, -Date) as Notes

Inline [

Date, Class, Grade, Teacher, Notes

2017-05-29 10:25:32:234, Math, 6, Ms Walsh, dafd

2017-05-20 9:06:42:345, Science, 10, Ms Helen, dafdaf

2017-05-20 23:33:46:453, Chemistry, 6, Mr Tom, dfdfd ]

Re: select the latest date data

userid128223 — Wed, 31 May 2017 05:27:54 GMT

i will try it out. I was thinking of doing via group by clause with max(date).

Re: select the latest date data

userid128223 — Wed, 31 May 2017 15:13:09 GMT

Hi Tresesco

2 question:

1) i tried your solution and with inline data it works but when you do it out of a file or with data generated by another load previously it does not work.

Below does not work:

temp:

LOAD Date,

Class,

Grade,

Teacher,

Notes

FROM

(ooxml, embedded labels, table is Sheet1);

new:

Load

Timestamp(Max(Date)) as Date,

FirstSortedValue(Class, -Date) as Class,

FirstSortedValue(Grade, -Date) as Grade,

FirstSortedValue(Teacher, -Date) as Teacher,

FirstSortedValue(Notes, -Date) as Notes

Resident temp;

drop table temp;

2) can this solution be derived by another method like max date & group by?

Re: select the latest date data

tresB — Thu, 01 Jun 2017 05:39:45 GMT

Just try putting a NoConcatenate between two loads like:

temp:

LOAD Date,

Class,

Grade,

Teacher,

Notes

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

new:

Load

Timestamp(Max(Date)) as Date,

FirstSortedValue(Class, -Date) as Class,

FirstSortedValue(Grade, -Date) as Grade,

FirstSortedValue(Teacher, -Date) as Teacher,

FirstSortedValue(Notes, -Date) as Notes

Resident temp;

drop table temp;