How to load records based on the latest date

[pranaview]

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

[shiveshsingh]

Can you share some sample data?

Firstsortedvalue can be used to get the desired result.

[shiveshsingh]

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

];

[Frank_Hartmann]

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

[pranaview]

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.

[Qliklearner3]

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

[rajpreeths]

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.