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

DougG

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?

Unknown

Hi, @DougG ,

 

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

JasonAltenburg

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.  

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

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.

 

JasonAltenburg

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.  

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

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.

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'

JasonAltenburg

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.  

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

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.

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'

JasonAltenburg

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.  

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

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.

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'

Unknown

Hi, @DougG ,

 

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

JasonAltenburg

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.  

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

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.

 

JasonAltenburg

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.  

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

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.