Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Days between Orders

I am trying to calculate the number of days since the previous order for a given customer and item combination when loading an order file in the load script.  For example, I have an order file that looks like this:

OrderNumber, OrderDate, Customer, Item:

Order123, 01/01/2013, ABC, ItemA,

Order129, 01/26/2013, ABC, ItemA,

Order130, 02/02/2013, ABC, ItemA,

Order124, 01/01/2013, ABC, ItemB,

Order128, 01/15/2013, ABC, ItemB,

Order131, 02/16/2013, ABC, ItemB,

Order125, 01/01/2013, ABC2, ItemA,

Order126, 01/05/2013, ABC2, ItemA,

Order127, 01/15/2013, ABC2, ItemA

And, what I want to end up with is a file that looks like this after the load script is executed:

OrderNumber, OrderDate, Customer, Item, DaysSincePreviousOrder:

Order123, 01/01/2013, ABC, ItemA, 0

Order129, 01/26/2013, ABC, ItemA, 25

Order130, 02/02/2013, ABC, ItemA, 7

Order124, 01/01/2013, ABC, ItemB, 0

Order128, 01/15/2013, ABC, ItemB, 14

Order131, 02/16/2013, ABC, ItemB, 31

Order125, 01/01/2013, DEF, ItemA, 0

Order126, 01/05/2013, DEF, ItemA, 4

Order127, 01/15/2013, DEF, ItemA, 10

Any suggestions would be greatly appreciated!

1 Reply
Not applicable
Author

Please excuse the inconsistency in my customer numbers from the previous post.  Customer ABC2 should have been DEF in the original order file.  Here is a more consistent data set:

OrderNumber, OrderDate, Customer, Item:

Order123, 01/01/2013, ABC, ItemA,

Order129, 01/26/2013, ABC, ItemA,

Order130, 02/02/2013, ABC, ItemA,

Order124, 01/01/2013, ABC, ItemB,

Order128, 01/15/2013, ABC, ItemB,

Order131, 02/16/2013, ABC, ItemB,

Order125, 01/01/2013, DEF, ItemA,

Order126, 01/05/2013, DEF, ItemA,

Order127, 01/15/2013, DEF, ItemA

Desired result after load script:

OrderNumber, OrderDate, Customer, Item, DaysSincePreviousOrder:

Order123, 01/01/2013, ABC, ItemA, 0

Order129, 01/26/2013, ABC, ItemA, 25

Order130, 02/02/2013, ABC, ItemA, 7

Order124, 01/01/2013, ABC, ItemB, 0

Order128, 01/15/2013, ABC, ItemB, 14

Order131, 02/16/2013, ABC, ItemB, 31

Order125, 01/01/2013, DEF, ItemA, 0

Order126, 01/05/2013, DEF, ItemA, 4

Order127, 01/15/2013, DEF, ItemA, 10