Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Can you provide some sample data?
Yes if you can provide the data as well as i am unclear about client column that what it conveys?
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..
Thanks a lot Deepak...I am really amazed with the knowledge you have
Here is the sample data
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);
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;
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
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
Hi all,
can this be done using mapping load?