topic Recursive Problem in QlikView

Recursive Problem

Sat, 01 May 2010 01:13:46 GMT

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.

Recursive Problem

johnw — Sat, 01 May 2010 01:31:11 GMT

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)

Recursive Problem

Sat, 01 May 2010 01:51:08 GMT

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.

Recursive Problem

Tue, 04 May 2010 22:05:48 GMT

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.