Is there an In Database Equivalent to the Appending Field Tool

[danielkresina]

The Append Fields tool in the standard Alteryx Join toolset is really handy when you just need to easily add some value to every row in it's own column.  It essentially does the equivalent of a cartesian join in TSQL where you join two tables without specifying the join condition so it essentially just mergest the two together.

The In Database tool set doesn't have a tool like this and you can't simply use the normal Join tool since it requires you to pick a field to join on.  In Database tools also don't allow you to use constants in your formulas or filters making it even more necessary to append individual values into your data stream in order to use them later. My work-arround has been to use the formula tool to add a dummy field to both streams with a single value such as 1 and use that field to join on in the join tool.  It works, but it feels like there has to be a better way.

How have the rest of you been handling this?

[SeanAdams]

Hey @danielkresina

I agree with your assessment - I do exactly the same to achieve the outcome you're looking for.

Just to make sure - you've got a main table (n rows) and you want to force a carteisan join with another table (m rows) to get the cross-product (nxm rows).    If so - then I agree with you on 3 counts:

1. This is not currently a drop-in capability in in-DB tools
2. The workaround right now is to create a synthetic field on both streams (e.g. "JoinField" = 1) and force a join using that field
3. This would be a useful capability to have - it's worth you suggesting this as a product idea in the Ideas forum here (and I'll vote for it): https://community.alteryx.com/t5/Alteryx-Product-Ideas/idb-p/product-ideas

Cheers @danielkresina - have a good week

Sean

[simonaubert_bd]

Hello @danielkresina 

As previously mentioned, no such feature in Alteryx but you can have a turnaround with creating a dummy field on each side of the join.

The idea is now created if you want to add your vote
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Cross-join-In-DB/idi-p/712314

Best regards,
Simon