Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rviel_dubo
Contributor III
Contributor III

Max date per product on order

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

   

OrderNoProductDelivered
2659611682410/03/16
2659611689110/06/16
265961169047510/05/16
265961169449609/21/16
2659033719510/12/16
2659033797410/17/16
26590331156010/12/16
26590331156110/12/16
265903320018610/12/16

Thanks in advance

Robert

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;

1.png

View solution in original post

3 Replies
maxgro
MVP
MVP

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;

1.png

sunny_talwar

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;

Capture.PNG

rviel_dubo
Contributor III
Contributor III
Author

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