KNOWLEDGE BASE

How to Create a Combination of Products Purchased for Each Order


Published: 28 Dec 2023
Last Modified Date: 29 Dec 2023

Question

How can we create a combination of products purchased for each order?

For example, assume that we have the following data.
User-added image

If we would like to create a combination of products purchased for each order as follows, how can it be achieved?
User-added image

Environment

  • Tableau Prep Builder

Answer

 Follow along in the sample packaged flow found in the Attachments section to create a combination of products purchased for each order.
User-added image

1. Add a Clean step to Input step.
2. Add a calculated field and name it ROW_NUMBER.
{PARTITION [Order ID] : {ORDERBY [Product Name] ASC : ROW_NUMBER() }}
3. Add Pivot step.
4. In the Pivoted Fields pane, select Rows to Columns from the drop-down list.
5. Select ROW_NUMBER from the left pane, and drag it to Field that will pivot rows to columns section.
6. Select Product Name from the left pane, and drag it to Field to aggregate for new columns section. Change the aggregation type to MIN.
 User-added image
7. Add Aggregate step.
8. Select Order ID, Date and Customer Name from the left pane, and drag them to Grouped Fields section.
9. Select 1, 2, 3 from the left pane, and drag them to Aggregated Fields section. Change the aggregation type to MIN.
 User-added image
10. Add Clean step.
11. Create a calculated field and name it Combination of Product Names.
[1]
+ IF NOT ISNULL([2]) THEN " | " ELSE "" END
+ IF NOT ISNULL([2]) THEN [2] ELSE "" END
+ IF NOT ISNULL([3]) THEN " | " ELSE "" END
+ IF NOT ISNULL([3]) THEN [3] ELSE "" END
12. Remove 1, 2, 3 fields in the Profile pane.
Did this article resolve the issue?