Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

9 Replies
MK_QSL
MVP
MVP

Can you provide some sample data?

Not applicable
Author

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

Not applicable
Author

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..

Not applicable
Author

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

Not applicable
Author

Here is the sample data

maxgro
MVP
MVP


just an idea, no set analysis, only script


RESULT

1.png

SCRIPT

Directory;

Source:

LOAD [Client Name],

     Product,

     [Purchase Date]

FROM

PData.xlsx

(ooxml, embedded labels, table is Sheet1);

T:

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;

MarcoWedel

Hi David,

that's been an interesting one.

Here is my approach to it:

QlikCommunity_Thread_112736_Pic1.JPG.jpg

QlikCommunity_Thread_112736_Pic2.JPG.jpg

QlikCommunity_Thread_112736_Pic5.JPG.jpg

QlikCommunity_Thread_112736_Pic3.JPG.jpg

QlikCommunity_Thread_112736_Pic4.JPG.jpg

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;

QlikCommunity_Thread_112736_Pic6.JPG.jpg

regards

Marco

Not applicable
Author

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

adarsh_a
Contributor III
Contributor III

Hi all,

can this be done using mapping load?