Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
JerzyD
Contributor
Contributor

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

Hello

I have the data:
orderidstatustimeindeksoperators
BB0100009.07.2021 12:12:511402196
CB0100009.07.2021 12:12:521502196
CB0700009.07.2021 15:41:241857103
BB0700009.07.2021 16:35:541871103
BB0700012.07.2021 09:44:282287103
BB0700012.07.2021 11:14:222325151
BB0700012.07.2021 11:55:412330103
CB0500012.07.2021 17:41:052360259
BB0500012.07.2021 18:01:582424259
BB0700015.07.2021 12:33:052899151
CB0700016.07.2021 11:07:392932151
AB0100016.07.2021 11:34:462971196
AB0500016.07.2021 17:49:06320890
AB0600019.07.2021 08:37:4132840
CB0700030.07.2021 13:15:034130187
the resulting data after loading the script should look like this:

orderidstatustimeindeksoperators
AB0600019.07.2021 08:37:4132840
BB0700015.07.2021 12:33:052899151
CB0700030.07.2021 13:15:034130187

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


 

Labels (1)
1 Solution

Accepted Solutions
joaopaulo_delco
Partner - Creator III
Partner - Creator III

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.

 

Help users find answers! Don't forget to mark a solution that worked for you!

View solution in original post

2 Replies
joaopaulo_delco
Partner - Creator III
Partner - Creator III

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.

 

Help users find answers! Don't forget to mark a solution that worked for you!
JerzyD
Contributor
Contributor
Author

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