Dynamically pasting the row values by matching the column header

I have a data coming in the following format post harmonisation

I wish to fill the values in the column in an empty data table by matching the headers from the data I have from harmonisation. I tried to add empty columns but the report will contain column names changing everyday, for example, the data for tuesday will only contain qty_monday and for wednesday it will contain qty_tuesday. I need to match the header names from the table and fill the rows corresponding to the column name in the empty data table I have required for the final format.

Screenshot from 2017-11-07 12_13_53.png Empty Table for output.jpeg Final Output req.jpeg

Help

Accepted answers

JordanB

Hi @Aniruddha

The join will be automatic as it just references a field called 'Name' because of the transpose and the code field appears in both of your datasets each time.

Please refer to example

Best,

Jordan

All comments

JordanB

Hi @Aniruddha

They key here is to try and get the field headers into one column so you can join between both of your tables.

In the attached workflow I transpose both datasets so you can join on both the code (row ID) and the field headers.

Therefore, Mondays data will only join with the monday field header and the data will go onto the correct row based on the code value.

You can then simply union the data and crosstab it back into your table format.

This will be dynamic so as your harmonized dataset updates to Tuesday for example the workflow will work.

See workflow for additional annotations (You will need to replace the text inputs for your raw data inputs).

Best,

Jordan Barker

Solutions Consultant

Sample workflow.yxmd

Aniruddha

Hi Jordan,

Thanks for the answer. However, each day I would be needing to configure the join node manually, which also I wanted to do dynamically. However, your answer does help us. Thanks

JordanB

Hi @Aniruddha

The join will be automatic as it just references a field called 'Name' because of the transpose and the code field appears in both of your datasets each time.

Please refer to example

Best,

Jordan

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335