How select columns to merge dynamically?

[RVC2023]

This is probably a newbie question, but any help is appreciated.

I have a workflow with 3 tables.

• ‘Projects’ contains project ID, and a field that identifies the name of a column to be merged.
• ‘Transactions’ contains transaction id, date elements (year/Month)
• ‘Resource’ contains a date key (year/Month) and 50 columns of resource data

Currently, I am merging the Transactions table to the Resource table based on the date key which is common to both. But as a result I have 49 columns of junk resource data. I then need to manually select which columns to remove in a later step with Table Manipulator. Ideally this could be automated.

What I would like to do is use the Projects table to identify precisely which column from the Resource table is needed (perhaps as a flow variable?) and use that to control which columns are merged when joining the Transactions table to the Resource table. So far I haven’t figured out the magic combinations of nodes /workflow to do this.

Thanks for any help.

[Daniel_Weikert]

Have you checked out Table Validator node? This can check and specify how a table should look based on references
br

[denisfi]

Hi,

Some questions before…

1- Do you need to bring all fields/columns from theses tables? Could you remove directly from the source before using it as a full table?

2- At the join process (at database is better then join node), you can select which columns you really need to select and use, removing the others “garbage” data. Did you try it?

If you can bring an example about this flow, it can help us to help you… lol

Seeya,

Denis

[ipazin]

Hello @RVC2023,

check out Reference Column Filter node.

Welcome to Community!

Br,
Ivan

[RVC2023]

@Daniel_Weikert

This can check and specify how a table should look based on references

Thank you, Daniel! I looked at this and couldn’t see how this makes the column selection dynamic. That said, learning about this node has been super-helpful for another problem I was working on. When sending data to PowerBI, I needed to enforce a fixed column and data type specification. This node allows me to enforce some of those parameters.

[mlauber71]

@RVC2023 you could create ‘dummy’ Join variables and fill them with Flow Variables. If you have varying numbers you might be able to repeat columns.

InformedJoin - how to pass flow variable for joined columns in join

I think what you would have to do is prepare as many ‘dummy’ Join entries as you would like to have in advance. If you have fewer you might try to set one ID twice (should not affect the outcome). [image] [image] [image] kn_example_joiner_variables_flow.knwf (22.7 KB)

[morpheus]

Hi @RVC2023,
an example would be very helpful for finding a solution for your problem.

I’m not sure if i get your problem. But let me have a try.

Your project table contains the columnname information of one or more of the 50 columns in the table resource and the date column should then be used to merge the related transaction id of the transaction table. Correct?

A possible option could be to unpivot your resurce table keeping the date key as identifier and then join the project table with unpivoted table and the transaction with the date key of the unpivoted table by using inner join.

BR