Scenario:

The data has date wise order details such as Order Id, Products and Order amount.


Requirement:

Find out the maximum repeated combination of dimension values from the data (e.g. Milk and bread are sold together maximum times).


Sample Data

The sample data is also attached herewith.




Step:1 

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



Step: 2

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