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

n____l____ · 2022-08-28T18:47:31+00:00

There was an error rendering this rich post.

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?

Find more posts tagged with

Dataset

Magic ETL

Table

Formatting

Data Flows

Accepted answers

MichelleH

@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

All comments

MichelleH

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

jaeW_at_Onyx

@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.