Recursive Problem

[#1]

I'm having problem with the recursion, as i need to find the following from the same table.

For my problem i'm taking product orders as sample data.

Problem : I need to find count of all the products which was ordered by same person within 72 hours of initial order.

Product Orders:

ProductId OrderDate OrderByUserId

1 12/12/10 3:45 1

2 12/12/10 4:45 2

1 12/13/10 3:45 1

2 12/12/10 4:45 33

2 12/16/10 4:45 12

2 12/12/10 4:45 2

Problem : I need to find count of all the products which was ordered by same person within 72 hours of initial order.

I'm not even sure if we can do this in Qlikview, Any help would be greatly appreciated.

[johnw · Accepted solution]

If by "initial order" you mean the first order for that customer, ever, regardless of any selections, perhaps handle it in script.

LEFT JOIN ([Product Orders])
LOAD
YourUniqueKeyField
,if(OrderTimestamp<=FirstOrderTimestamp+3,'Y','N') as OrderedWithin72Hours?
;
LOAD
YourUniqueKeyField
,OrderTimestamp
,if(OrderByUserId<>previous(OrderByUserId),OrderTimestamp,peek(FirstOrderTimestamp)) as FirstOrderTimestamp
RESIDENT [Product Orders]
ORDER BY OrderByUserId, OrderTimestamp
;

Then your chart is pretty simple:

Dimension = OrderByUserId
Expression = count({<OrderedWithin72Hours?={'Y'}>} distinct ProductId)

[#3]

Thank You John,

Let me try this, i tried something similar but in a different way(SQL recursive query) but your solution seems better as what i did was extremely slow considering 2 mil+ rows .

Thanks for quick reply.

[#4]

Hi John,

Thank you for the solution, it worked fine, the only problem i had was that i had multiple dates to compare, it became really complicated load but it's working like a charm.

Thanks,

Nish.