Remove records with same date and display only max date records with same date

[sruthi19]

Hi Team,

Need to display only Max Insert_Timestamp records if it has same date.

On 18 it has 2 records, i need to display only Max(Insert_Timestamp) and remaining records.

Input
ID	LoadErrors	Insert_Timestamp	Environment
321	5	2022-04-25 14:04:37	System
634	10	2022-04-25 14:04:37	Clock
221	6	2022-04-18 12:10:50	System
432	2	2022-04-18 12:10:50	Clock
345	1	2022-04-18 12:08:15	System
234	8	2022-04-18 12:08:15	Clock
342	12	2022-03-10 07:10:25	System
235	4	2022-03-10 07:10:25	Clock
Output
ID	LoadErrors	Insert_Timestamp	Environment
321	5	2022-04-25 14:04:37	System
634	10	2022-04-25 14:04:37	Clock
221	6	2022-04-18 12:10:50	System
432	2	2022-04-18 12:10:50	Clock
342	12	2022-03-10 07:10:25	System
235	4	2022-03-10 07:10:25	Clock

[Frank_E-W]

You could have a look at this script function and see if it's helpful:

FirstSortedValue - script function ‒ Qlik Sense on Windows

[OmarBenSalem]

18? which 18?

well..

It would be something like this :

YourTable:

load

ID,

LoadErrors,

Insert_Timestamp,

Environment 

from source;

right join

load ID,

timestamp(max(Insert_Timestamp),'YYYY-MM-DD hh:mm:ss') as Insert_Timestamp

resident YourTable group by ID;

[sruthi19]

Hi,

Thanks for your quick response.

Code in the Load Editor is :

[COUNT_TABLE]:
SELECT "ID",
"LOADERRORS",
"Insert_Timestamp",
"Environment "
FROM "Table"."COUNT_TABLE";

LOAD ID,
LOADERRORS,
Insert_Timestamp,
Environment ;

As i am new to the Qliksense , can you please help me how to use Join for the above code.

[sruthi19]

Hi Team,

Can any one help me on this.

Either  in chart function expression  or in data load editor.

Thanks

[vinieme12]

As below

tmp:
Load *,Date(floor(timestamp#(Insert_Timestamp,'YYYY-MM-DD hh:mm:ss'))) as insert_Date inline [
ID,LoadErrors,Insert_Timestamp,Environment
321,5,2022-04-25 14:04:37,System
634,10,2022-04-25 14:04:37,Clock
221,6,2022-04-18 12:10:50,System
432,2,2022-04-18 12:10:50,Clock
345,1,2022-04-18 12:08:15,System
234,8,2022-04-18 12:08:15,Clock
342,12,2022-03-10 07:10:25,System
235,4,2022-03-10 07:10:25,Clock
];

Right Join(tmp)
Load
insert_Date,Environment,timestamp(max(Insert_Timestamp),'YYYY-MM-DD hh:mm:ss') as Insert_Timestamp
Resident tmp
Group by insert_Date,Environment
;
exit Script;