Data in the max DateTime

[harson]

Hello community,

I have a hang on QlikView.

I have as data this:

I would like to retrieve the lines with which the DateTime for each File_ID is the max of DateTime (marked in yellow)

Representing them in a table like this:

Thanks in advance for your help

Parts of Data :

LOAD * INLINE [

    Agents, File_ID, DateTime, Status

    John DOE, 622, 13/06/2018 16:29, 70

    John DOE, 622, 13/06/2018 12:48, 70

    John DOE, 622, 14/06/2018 12:29, 70

    John DOE, 622, 15/06/2018 10:27, 71

    John DOE, 633, 16/06/2018 07:29, 65

    John DOE, 633, 12/06/2018 08:26, 65

    John DOE, 611, 11/06/2018 12:25, 85

    Arold FAA, 611, 11/06/2018 12:39, 93

    John DOE, 352, 10/06/2018 12:35, 95

    John DOE, 352, 10/06/2018 12:45, 58

    Arold FAA, 633, 11/06/2018 09:07, 65

    John DOE, 352, 15/06/2018 12:48,

    John DOE, 352, 10/06/2018 12:36, 63

    John DOE, 611, 11/06/2018 12:52, 63

    John DOE, 611, 11/06/2018 12:23, 65

    Arold FAA, 633, 16/06/2018 12:29, 70

];

[sunny_talwar · Accepted solution]

May be by creating a flag in the script

Table:

LOAD *,

Date(Floor(DateTime)) as Date;

LOAD * INLINE [

    Agents, File_ID, DateTime, Status

    John DOE, 622, 13/06/2018 16:29, 70

    John DOE, 622, 13/06/2018 12:48, 70

    John DOE, 622, 14/06/2018 12:29, 70

    John DOE, 622, 15/06/2018 10:27, 71

    John DOE, 633, 16/06/2018 07:29, 65

    John DOE, 633, 12/06/2018 08:26, 65

    John DOE, 611, 11/06/2018 12:25, 85

    Arold FAA, 611, 11/06/2018 12:39, 93

    John DOE, 352, 10/06/2018 12:35, 95

    John DOE, 352, 10/06/2018 12:45, 58

    Arold FAA, 633, 11/06/2018 09:07, 65

    John DOE, 352, 15/06/2018 12:48

    John DOE, 352, 10/06/2018 12:36, 63

    John DOE, 611, 11/06/2018 12:52, 63

    John DOE, 611, 11/06/2018 12:23, 65

    Arold FAA, 633, 16/06/2018 12:29, 70

];

Left Join (Table)

LOAD File_ID,

Max(DateTime) as DateTime,

1 as MaxFlag

Resident Table

Group By File_ID;

and then using

=Count(DISTINCT {<MaxFlag = {1}>} File_ID)

[harson]

Thanks stalwar1

You are the best

[Paresh]

Hello @sunny_talwar , I want to get  the value at max datetime, if the value at max datetime is null, then check from the Current day to the last  3 days on the Outlet and Dispenser