Using Columns-to-Rows before a join.
I'm using version 126.96.36.199 and have encountered the following phenomenon.
When using the Columns-to-Rows step and using a Right-unmatched or Full outer join after it, all the rows that have no match in the Left table are still populated with the first row of the Left table. The expected result, at least for me would be to leave every column coming from the Left table empty, and only have records in the Right columns.
In the example below (JPG and test data attached), you can also see this happening. I am joining the two tables on the Key3(Left) and Key(Right) columns, however the result is populated on the "Left" side as well. If you reverse the inputs and try a left-unmatched join, it yields the same result, only in reversed column order
This does not happen with other steps, but Columns-to-rows seems to "lock" the columns in a way that would make a join not work as expected.
Could you please let me know if there is a way to circumvent this issue?
Thanks in advance.
Hi @Richard Illyes - I've reproduced this, thanks for attaching the workflow and test data to make it easy! I've just created a ticket for our engineering team to pick up. In terms of a workaround, what worked for me was feeding the output of the Columns To Rows step into a snapshot, and then (in a second workflow) using the snapshot as the left side input to the join. This basically means splitting the workflow into two.
Just to confirm this was resolved in the latest release https://community.experianaperture.io/discussion/922/aperture-data-studio-2-9-1