Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you share some sample data?
Firstsortedvalue can be used to get the desired result.
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
];
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 |
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.
Hi @pranaview did you get any solution for your last question I have the same issue I've multiple fields.
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.