Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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!

Tags (1)
1 Reply
Not applicable

Re: Days between Orders

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

Community Browser