what condition should be entered in the script for recent orders?

[JerzyD]

Hello

I have the data:

orderid	status	time	indeks	operators
B	B01000	09.07.2021 12:12:51	1402	196
C	B01000	09.07.2021 12:12:52	1502	196
C	B07000	09.07.2021 15:41:24	1857	103
B	B07000	09.07.2021 16:35:54	1871	103
B	B07000	12.07.2021 09:44:28	2287	103
B	B07000	12.07.2021 11:14:22	2325	151
B	B07000	12.07.2021 11:55:41	2330	103
C	B05000	12.07.2021 17:41:05	2360	259
B	B05000	12.07.2021 18:01:58	2424	259
B	B07000	15.07.2021 12:33:05	2899	151
C	B07000	16.07.2021 11:07:39	2932	151
A	B01000	16.07.2021 11:34:46	2971	196
A	B05000	16.07.2021 17:49:06	3208	90
A	B06000	19.07.2021 08:37:41	3284	0
C	B07000	30.07.2021 13:15:03	4130	187

the resulting data after loading the script should look like this:

orderid	status	time	indeks	operators
A	B06000	19.07.2021 08:37:41	3284	0
B	B07000	15.07.2021 12:33:05	2899	151
C	B07000	30.07.2021 13:15:03	4130	187

How do I write a script so that only unique records are loaded for the "order id" dimension, assuming the "index" dimension has the highest value?

I will be grateful for the solution

Best regards
JD

[joaopaulo_delco · Accepted solution]

Hi @JerzyD !

I think the script below will solve your problem.

[TABLE_ORIGINAL]:
Load * Inline [
orderid,	status,	time,	indeks,	operators
B,	B01000,	09.07.2021 12:12:51,	1402,	196
C,	B01000,	09.07.2021 12:12:52,	1502,	196
C,	B07000,	09.07.2021 15:41:24,	1857,	103
B,	B07000,	09.07.2021 16:35:54,	1871,	103
B,	B07000,	12.07.2021 09:44:28,	2287,	103
B,	B07000,	12.07.2021 11:14:22,	2325,	151
B,	B07000,	12.07.2021 11:55:41,	2330,	103
C,	B05000,	12.07.2021 17:41:05,	2360,	259
B,	B05000,	12.07.2021 18:01:58,	2424,	259
B,	B07000,	15.07.2021 12:33:05,	2899,	151
C,	B07000,	16.07.2021 11:07:39,	2932,	151
A,	B01000,	16.07.2021 11:34:46,	2971,	196
A,	B05000,	16.07.2021 17:49:06,	3208,	90
A,	B06000,	19.07.2021 08:37:41,	3284,	0
C,	B07000,	30.07.2021 13:15:03,	4130,	187
];

[AUX_FILTER]:
Load
    (orderid & '|' & maxindex) as Key_filter;
Load
    orderid,
    Max(indeks) as maxindex
Resident TABLE_ORIGINAL Group by orderid;

Rename Table TABLE_ORIGINAL to TABLE_ORIGINAL_OLD;

NoConcatenate
[TABLE_ORIGINAL]:
Load 
    orderid,	
    status,	
    time,	
    indeks,	
    operators
Resident TABLE_ORIGINAL_OLD where Exists(Key_filter,(orderid & '|' & indeks));    

Drop Tables TABLE_ORIGINAL_OLD, AUX_FILTER;

Please mark as solve if it solve your problem.

[JerzyD]

Hello

Thank You!

A similar solution is:

filtr:
Inner Keep LOAD
orderid, max(indeks) as indeks
Resident xxxxx
group by orderid;
drop table filtr;

Best regards!

JD