Is there a way other than SQL to cross-join two tables (Cartesian Product)?

Reply
Visitor

Is there a way other than SQL to cross-join two tables (Cartesian Product)?

I know that I can use SQL to cross-join two tables. However, that step is only a portion of my dataflow and I would like to use for the rest of the dataflow. Is there a way to cross-join in ETL or do I need to run my cross-join as a separate dataflow in SQL and use the output in my ETL?

Brown Belt

Re: Is there a way other than SQL to cross-join two tables (Cartesian Product)?

Hi, @user01408 ,

 

I did this recently. It isn't elegant, but it works just fine.

 

1) In each of the two tables you want to cross-join, add a whole number constant field using the "Add Constant" tiles. In both cases, assign some dummy value. It doesn't matter what value as long as you use the same value in both tiles.

2) Join the two datasets using your dummy constant fields

 

Note: this is equivalent to joining two tables in SQL using "on 1 = 1" in the join condition

Yellow Belt

Re: Is there a way other than SQL to cross-join two tables (Cartesian Product)?

I think you would be better off doing the SQL step, but I think it is possible in ETL as follows.

 

I utilized the tables from the cross join example on w3resource here

 

After adding these tables using excel upload to function as inputs, I used add constant to the company table of an ITEM_UNIT column containing string 'Pcs', to match the ITEM_UNIT field in the foods table.  

add_constants.png

I then did a full outer join on the ITEM_UNIT field.

join_data.png

I then did select columns to only return the desired fields of ITEM_NAME, ITEM_UNIT, COMPANY_NAME, and COMPANY_CITY.

 

This produced what I believe is the desired output.

output_preview.png

 

Yellow Belt

Re: Is there a way other than SQL to cross-join two tables (Cartesian Product)?

I think you would be better off doing the SQL step, but I think it is possible in ETL as follows.

 

I utilized the tables from the cross join example on w3resource here

 

After adding these tables using excel upload to function as inputs, I used add constant to the company table of an ITEM_UNIT column containing string 'Pcs', to match the ITEM_UNIT field in the foods table.  

add_constants.png

I then did a full outer join on the ITEM_UNIT field.

join_data.png

I then did select columns to only return the desired fields of ITEM_NAME, ITEM_UNIT, COMPANY_NAME, and COMPANY_CITY.

 

This produced what I believe is the desired output.

output_preview.png

 

Announcements
Dojo Day is Coming! Mark your calendars for May 30th 0900-1700 MST Click here for more information