topic Re: How to load records based on the latest date in QlikView

How to load records based on the latest date

pranaview — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I've got a small problem.I have a table based on Order numbers and it also has a Delivery Date column. An order number may have multiple records with different delivery date. I want to pick just one record for one order number with the latest(max) delivery date.Any help will be appreciated.

P.S. I've just started learning Qlik tools

Re: How to load records based on the latest date

shiveshsingh — Fri, 21 Sep 2018 15:49:40 GMT

Can you share some sample data?

Firstsortedvalue can be used to get the desired result.

Re: How to load records based on the latest date

shiveshsingh — Fri, 21 Sep 2018 15:55:40 GMT

Or you can get idea form below script

T: LOAD MAX(NUMBER) AS NUMBER, NAME

GROUP BY NAME;

LOAD * INLINE [

NAME, NUMBER

A, 1

A, 2

A, 3

B, 4

B, 5

B, 6

C, 7

C, 8

C, 9

];

Re: How to load records based on the latest date

Frank_Hartmann — Fri, 21 Sep 2018 18:43:21 GMT

Maybe like that:

tmp0:

LOAD * INLINE [

ID, Date, Data

1, 01.03.2018, 245

2, 04.03.2018, 453

3, 02.03.2018, 46

1, 03.03.2018, 65

2, 09.03.2018, 756

3, 07.03.2018, 23

];

NoConcatenate

Load ID, max(Date) as Date, FirstSortedValue(Data,-Date) as Data Resident tmp0 Group by ID; DROP table tmp0

Result:

ID	Date	Data
1	03.03.2018	65
2	09.03.2018	756
3	07.03.2018	23

Re: How to load records based on the latest date

pranaview — Wed, 26 Sep 2018 13:03:07 GMT

Hi Frank,

Thanks for the solution. I tried and it works. Just one question, In your example you have one field i.e. Data but i have like 10 fields, so I'll have to use FirstSortedValue() function on each field to get the desired row.

Is this the right way to achieve this or is there any other alternative to it?

Again, thanks for you reply.

Re: How to load records based on the latest date

Qliklearner3 — Wed, 04 Aug 2021 11:38:59 GMT

Hi @pranaview did you get any solution for your last question I have the same issue I've multiple fields.

Re: How to load records based on the latest date

rajpreeths — Wed, 04 Aug 2021 18:21:41 GMT

When you have multiple fields in a table , please calculate latest delivery date per order and inner join with calculated table with main fact.this will remove unwanted records from the application.may be the sample code below

OrdersFact:

Load ordernum,

Product,

Deliverydate,x,y.

,Z.. from table

Innero join(OrdersFact)

Load Ordernum,

Max(DeliveryDate) as Deliverydate,

Max(1) as latestorder

Resident OrdersFact

Group by ordernum.

Please ensure you use necessary date fucntions to have a proper joining to happen between the tables.

Apologies for the typos in the code. I just typed using my mobile.you can follow the same approch with your data set.it has to work.if not ,provide sample data.