Find out the frequency of repetition of dimension value combinations – e.g. frequency of combination of bread and butter from sales transactions.
The data has date wise order details such as Order Id, Products and Order amount.
Find out the maximum repeated combination of dimension values from the data.
(e.g. Milk and bread are sold together maximum times).
The sample data is also attached herewith.
1.Create a copy (Dt_Cross_Sales_B ) of the main dataset (Dt_Cross_Sales_A) to find out the combination count between the data.
2.Create a custom column called count with value as 1 for getting the total repeated count.
a. Right click on the cell and click on Add Column -> Custom.
Create a Custom Column
Custom Column expression
Use Self Join to get Product A against Product B.
1.Add the copied dataset - Dt_Cross_Sales_B to the main dataset - Dt_CrossSales_A
2.Apply blend JOIN and select the Dt_Cross_Sales_B dataset.
3.Use Inner JOIN based on Order ID = Order ID, Product_A != Product_B (for not getting the same product in the combination) and Date = Date.
Self join condition
The resultant dataset will have all the products against their combination products as below.
Result after Join
The output is given below in a crosstab with products and their combination products with their repetition count.
In the crosstab, take Product_A in the row and the Product_B in the column with Count as the data.
Product count against different products
The output is given below in a ‘Heat map’ graph with products and their combination products with their repetition count.
In the graph also, take Product_A in the row and the Product_B in the column with Count as the data.
Result plotted in a heat map