Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm looking to get the last delivered date for every product on an order, meaning qs > 0
Orders:
LOAD * Inline [
OrderNo, Delivered, Product, Qbo, Qs
2659611, 09/21/16, 6824, 1, 0
2659611, 10/03/16, 6824, 0, 1
2659611, 09/21/16, 690475, 1, 0
2659611, 10/03/16, 690475, 1, 0
2659611, 10/05/16, 690475, 0, 1
2659611, 09/21/16, 6891, 5, 4
2659611, 10/03/16, 6891, 1, 0
2659611, 10/05/16, 6891, 1, 0
2659611, 10/06/16, 6891, 0, 1
2659611, 09/21/16, 690476, 1, 0
2659611, 10/03/16, 690476, 1, 0
2659611, 10/05/16, 690476, 1, 0
2659611, 10/06/16, 690476, 1, 0
2659611, 09/21/16, 694496, 2, 1
2659611, 10/03/16, 694496, 2, 0
2659611, 10/05/16, 694496, 2, 0
2659033, 10/12/16, 7195, 0, 15
2659033, 10/12/16, 7974, 7, 0
2659033, 10/14/16, 7974, 1, 6
2659033, 10/17/16, 7974, 0, 1
2659033, 10/12/16, 11560, 0, 50
2659033, 10/12/16, 11561, 0, 20
2659033, 10/12/16, 11692, 3, 0
2659033, 10/12/16, 200186, 0, 2
];
I've tried --> Date(Aggr(Max({<Qs = {'>0'}>} Delivered), OrderNo, Product)) but that didn't worked
I've tried to script using Group By but again it failed
NoConcatenate Dates:
LOAD
OrderNo as Orn,
Product as Prn,
Max(Delivered) as LstDel
Resident Orders
Where Qs > 0
Group By OrderNo, Product;
I'm now turning on to you folks for any suggestions that give this expected result
OrderNo | Product | Delivered |
2659611 | 6824 | 10/03/16 |
2659611 | 6891 | 10/06/16 |
2659611 | 690475 | 10/05/16 |
2659611 | 694496 | 09/21/16 |
2659033 | 7195 | 10/12/16 |
2659033 | 7974 | 10/17/16 |
2659033 | 11560 | 10/12/16 |
2659033 | 11561 | 10/12/16 |
2659033 | 200186 | 10/12/16 |
Thanks in advance
Robert
SET DateFormat='MM/DD/YYYY';
Orders:
LOAD * Inline [
OrderNo, Delivered, Product, Qbo, Qs
2659611, 09/21/16, 6824, 1, 0
2659611, 10/03/16, 6824, 0, 1
2659611, 09/21/16, 690475, 1, 0
2659611, 10/03/16, 690475, 1, 0
2659611, 10/05/16, 690475, 0, 1
2659611, 09/21/16, 6891, 5, 4
2659611, 10/03/16, 6891, 1, 0
2659611, 10/05/16, 6891, 1, 0
2659611, 10/06/16, 6891, 0, 1
2659611, 09/21/16, 690476, 1, 0
2659611, 10/03/16, 690476, 1, 0
2659611, 10/05/16, 690476, 1, 0
2659611, 10/06/16, 690476, 1, 0
2659611, 09/21/16, 694496, 2, 1
2659611, 10/03/16, 694496, 2, 0
2659611, 10/05/16, 694496, 2, 0
2659033, 10/12/16, 7195, 0, 15
2659033, 10/12/16, 7974, 7, 0
2659033, 10/14/16, 7974, 1, 6
2659033, 10/17/16, 7974, 0, 1
2659033, 10/12/16, 11560, 0, 50
2659033, 10/12/16, 11561, 0, 20
2659033, 10/12/16, 11692, 3, 0
2659033, 10/12/16, 200186, 0, 2
];
left join (Orders)
LOAD
OrderNo,
Product,
Date(Max(Delivered)) as Delivered,
1 as FlagMax
Resident Orders
Where Qs > 0
Group By OrderNo, Product;
SET DateFormat='MM/DD/YYYY';
Orders:
LOAD * Inline [
OrderNo, Delivered, Product, Qbo, Qs
2659611, 09/21/16, 6824, 1, 0
2659611, 10/03/16, 6824, 0, 1
2659611, 09/21/16, 690475, 1, 0
2659611, 10/03/16, 690475, 1, 0
2659611, 10/05/16, 690475, 0, 1
2659611, 09/21/16, 6891, 5, 4
2659611, 10/03/16, 6891, 1, 0
2659611, 10/05/16, 6891, 1, 0
2659611, 10/06/16, 6891, 0, 1
2659611, 09/21/16, 690476, 1, 0
2659611, 10/03/16, 690476, 1, 0
2659611, 10/05/16, 690476, 1, 0
2659611, 10/06/16, 690476, 1, 0
2659611, 09/21/16, 694496, 2, 1
2659611, 10/03/16, 694496, 2, 0
2659611, 10/05/16, 694496, 2, 0
2659033, 10/12/16, 7195, 0, 15
2659033, 10/12/16, 7974, 7, 0
2659033, 10/14/16, 7974, 1, 6
2659033, 10/17/16, 7974, 0, 1
2659033, 10/12/16, 11560, 0, 50
2659033, 10/12/16, 11561, 0, 20
2659033, 10/12/16, 11692, 3, 0
2659033, 10/12/16, 200186, 0, 2
];
left join (Orders)
LOAD
OrderNo,
Product,
Date(Max(Delivered)) as Delivered,
1 as FlagMax
Resident Orders
Where Qs > 0
Group By OrderNo, Product;
You script seems to be working for me
Orders:
LOAD * Inline [
OrderNo, Delivered, Product, Qbo, Qs
2659611, 09/21/16, 6824, 1, 0
2659611, 10/03/16, 6824, 0, 1
2659611, 09/21/16, 690475, 1, 0
2659611, 10/03/16, 690475, 1, 0
2659611, 10/05/16, 690475, 0, 1
2659611, 09/21/16, 6891, 5, 4
2659611, 10/03/16, 6891, 1, 0
2659611, 10/05/16, 6891, 1, 0
2659611, 10/06/16, 6891, 0, 1
2659611, 09/21/16, 690476, 1, 0
2659611, 10/03/16, 690476, 1, 0
2659611, 10/05/16, 690476, 1, 0
2659611, 10/06/16, 690476, 1, 0
2659611, 09/21/16, 694496, 2, 1
2659611, 10/03/16, 694496, 2, 0
2659611, 10/05/16, 694496, 2, 0
2659033, 10/12/16, 7195, 0, 15
2659033, 10/12/16, 7974, 7, 0
2659033, 10/14/16, 7974, 1, 6
2659033, 10/17/16, 7974, 0, 1
2659033, 10/12/16, 11560, 0, 50
2659033, 10/12/16, 11561, 0, 20
2659033, 10/12/16, 11692, 3, 0
2659033, 10/12/16, 200186, 0, 2
];
NoConcatenate
Dates:
LOAD
OrderNo as Orn,
Product as Prn,
Date(Max(Delivered)) as LstDel
Resident Orders
Where Qs > 0
Group By OrderNo, Product;
Omg, I feel ashamed
Totally forgot to convert the date format.
Thanks maxgro to remind me to go back to basic when the obvious should have worked
Regards.
Robert