9 Replies Latest reply: Apr 4, 2014 2:34 AM by Adarsha A

# Possible Set Analysis question

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

Product Line

A

B

C

D

E

F

G

H

I

J

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

Can you provide some sample data?

• ###### Re: Possible Set Analysis question

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

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

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

• ###### Re: Possible Set Analysis question

Here is the sample data

• ###### Re: Possible Set Analysis question

just an idea, no set analysis, only script

RESULT

SCRIPT

Directory;

Source:

Product,

[Purchase Date]

FROM

PData.xlsx

(ooxml, embedded labels, table is Sheet1);

T:

NoConcatenate

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

Hi David,

that's been an interesting one.

Here is my approach to it:

```tabPurchases:
Product,
[Purchase Date]
FROM
(ooxml, embedded labels, table is Sheet1);

Left Join
[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:
[Client Name],
'Product'&PurchaseNumber as ProdNum,
Product
Resident tabPurchases;

```

regards

Marco

• ###### Re: Possible Set Analysis question

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

Hi all,

can this be done using mapping load?