topic Data in the max DateTime in QlikView

Data in the max DateTime

harson — Mon, 18 Jun 2018 08:42:18 GMT

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

];

Re: Data in the max DateTime

sunny_talwar — Mon, 18 Jun 2018 09:13:52 GMT

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)

Re: Data in the max DateTime

harson — Mon, 18 Jun 2018 10:33:28 GMT

Thanks stalwar1

You are the best

Re: Data in the max DateTime

Paresh — Tue, 14 Mar 2023 04:37:32 GMT

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