Attempting to create unique rows for each possible value in a chosen column

Our business model currently offers multiple product options. I'd like to create brand new rows for each customer, 1 for each product, whether they own that product or not. My thought is to use new columns and then use a dynamic unpivot. Need help creating the new columns first though..
Currently have this:
Account Product
xxxxxxx A
xxxxxxp B
xxxxxpp C
xxxxppp D
Trying to create:
Account Product A Product B Product C Product D
xxxxxxx A null null null
xxxxxxp null B null null
xxxxxpp null null C null
xxxxppp null null null D
End Goal:
Account Product
xxxxxxx A
xxxxxxx B
xxxxxxx C
xxxxxxx D
xxxxxxp A
and so on..
Does anyone have an idea of how to do this?
Best Answer
-
@n____l____ It sounds like you will need to use a cross-join to get to your end goal of every Account/Product combination (https://www.w3resource.com/sql/joins/cross-join.php). Since cross-joins aren't a pre-configured option in the ETL Join tiles, you'll to do the following as a work-around:
- Create two separate Remove Duplicates tiles branching off of your original data - One to remove duplicates by Account and one by Product
- Add Constants after each Remove Duplicates to create a column called "Cross-Join Key" with a value of 1
- Join both branches together using the Cross-Join Key. Since these all have the same value, it will match every row of each table
3
Answers
-
@n____l____ It sounds like you will need to use a cross-join to get to your end goal of every Account/Product combination (https://www.w3resource.com/sql/joins/cross-join.php). Since cross-joins aren't a pre-configured option in the ETL Join tiles, you'll to do the following as a work-around:
- Create two separate Remove Duplicates tiles branching off of your original data - One to remove duplicates by Account and one by Product
- Add Constants after each Remove Duplicates to create a column called "Cross-Join Key" with a value of 1
- Join both branches together using the Cross-Join Key. Since these all have the same value, it will match every row of each table
3 -
@n____l____ i urge you to reconsider this model.
if you build a dataset with an indeterminate number of columns (it would be based on the contents of the data collected that day) then you can't really build cards off of it. right, today you might have item_a, tomorrow you might not. so then your card in analyzer breaks.
you might pose the arguement that you'll always have item_a ... fair enough, but what if you didnt.
you could build a pivot table. wherere item_id is on the columns.
@MichelleH has the solution. The only addendum i'd advise is use a Product Dimension table and not Customer Sales Transactions as the basis for your distinct list of items.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 656 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 813 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 81 App Studio
- 45 Variables
- 771 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 77 Workflows
- 23 Code Engine
- 36 AI and Machine Learning
- 19 AI Chat
- AI Playground
- AI Projects and Models
- 17 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 142 Manage
- 138 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive