topic Possible Set Analysis question in QlikView

Possible Set Analysis question

Tue, 01 Apr 2014 11:57:11 GMT

I have a question on Set Analysis and I believe it might solve my next challenge. I have the following data set:

Product Line

I have the following information on my client list:

Product Purchased Client Purchase Date

A 1 1/1/2013

B 1 2/2/2013

D 1 5/13/2013

Now I have all of the above information for 4500 Clients that Purchased A as their first purchase.

What I want to determine is the following:

1. How many clients that Purchased A first purchased B second, A first then C, A first then D, until I get all the combinations. So the results would be:

Product Total Number

A and B 10

A and C 8

A and D 7

2. Other piece is how many days was it between the purchase of Product A and B, A and C, A and D, etc.

I would like to keep this simple with 2 product combinations even though their is a possibility that a client could purchase A, B, C as their first purchase and D as their 2nd. If this cant be done that is fine. I am thinking I might have to break this up into to different Set Analysis Charts. Thoughts are welcome.

Re: Possible Set Analysis question

MK_QSL — Tue, 01 Apr 2014 12:18:32 GMT

Can you provide some sample data?

Re: Possible Set Analysis question

Tue, 01 Apr 2014 12:38:40 GMT

Yes if you can provide the data as well as i am unclear about client column that what it conveys?

Re: Possible Set Analysis question

Tue, 01 Apr 2014 12:53:34 GMT

The data is about which client purchased a product and the date of purchase. Client columns is the name or ID of the customer of that product.

Hope it is clear to you now..

Re: Possible Set Analysis question

Tue, 01 Apr 2014 13:05:31 GMT

Thanks a lot Deepak...I am really amazed with the knowledge you have

Re: Possible Set Analysis question

Tue, 01 Apr 2014 13:06:23 GMT

Here is the sample data

Re: Possible Set Analysis question

maxgro — Tue, 01 Apr 2014 19:57:00 GMT

just an idea, no set analysis, only script

RESULT

SCRIPT

Directory;

Source:

LOAD [Client Name],

Product,

[Purchase Date]

FROM

PData.xlsx

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

load

if(peek([Client Name])<>[Client Name], 1, 1+peek(ProductOrder)) as ProductOrder,

[Client Name],

Product,

[Purchase Date]

Resident Source

order by [Client Name],

[Purchase Date]

;

DROP Table Source;

T2:

NoConcatenate

load * Resident T

Where ProductOrder = 1;

left join (T2) load

[Client Name],

Product as Product2,

[Purchase Date] as [Purchase Date2],

ProductOrder as ProductOrder2

Resident T

Where ProductOrder = 2;

DROP Table T;

Re: Possible Set Analysis question

MarcoWedel — Wed, 02 Apr 2014 00:23:23 GMT

Hi David,

that's been an interesting one.

Here is my approach to it:

tabPurchases:
LOAD [Client Name],
     Product,
     [Purchase Date]
FROM
[http://community.qlik.com/servlet/JiveServlet/download/499830-99688/PData.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join
LOAD Distinct
  [Client Name],
  [Purchase Date],
  If([Client Name]=Peek([Client Name]), Peek(PurchaseNumber, -1)+1, 1) as PurchaseNumber,
  If([Client Name]=Peek([Client Name]), [Purchase Date]-Peek([Purchase Date], -1)) as DaysSinceLastPurchase
Resident tabPurchases
Order By [Client Name], [Purchase Date];

tabProductSequence:
Generic LOAD
  [Client Name],
  'Product'&PurchaseNumber as ProdNum,
  Product
Resident tabPurchases;

regards

Marco

Re: Possible Set Analysis question

Wed, 02 Apr 2014 13:52:17 GMT

Hi Marco,

The way you answered this is awesome. It appears this works when you data set has only one product purchased at a time which is what I am after. The challenge I have is excluding those clients in my list that bought more than one product at a time which is all in the same table. I will work on that.

David

Re: Possible Set Analysis question

adarsh_a — Fri, 04 Apr 2014 06:34:07 GMT

Hi all,

can this be done using mapping load?