Transform the data into the Desired Format

[DataPirate26]

Hi all, 

Below I have attached a workflow which has a input dataset and that needs to be transformed into the Given Desired Output Format. 

Note: The Headers are in Row 1. Also I need to Capture all the columns dynamically till the keyword "END" is detected in the First Row. hence we need to capture details till F45. Also if the first Row is Null() Then that entire column needs to be discarded. "F2, F48, F49" needs to be discarded. 

The output shall have 3 columns - Entity Name, Account Name and Values. Row 1 contains the entity name, Column 1 has the Account Names and look into the desired output file for the corresponding values. I need to populate the values for each All the entities tied to all the accounts. 

[rzdodson]

@DataPirate26 when able, would you mind posting a packaged workflow so we can see the input data? Workflow is currently erroring out. Instructions can be found here.

[DataPirate26]

@rzdodson Here is the updated Workflow. Can you please check once 

[caltang]

To export a workflow go to: Options > Export Workflow. Kindly do NOT send a "Save As" copy.

[DataPirate26]

The dataset is small and doesn't need to export the workflow. I have provided the Updated workflow

[Qiu · Accepted solution]

@DataPirate26 
Give my shot

[DataPirate26]

@Qiu The solution actually needs to be dynamic so that we individually don't have to filter out the Entity Names. 

In the desired output that I have shared I have just shown the output for 2 Entity names as an example. But we need to capture the Entire dataset. So the First Row we need to capture all the Entity Names till the Keyword End is detected. Can we create the solution as dynamic

[Qiu]

@DataPirate26 
I did filter only want to confirm my result is consistent with yours.
The data before the last two filter is the complete set.

[DataPirate26]

@Qiu Sorry missed out the last part actually. The solution is perfect. Thank you so much for the quick resolution. 

[Qiu]

@DataPirate26 
Glad if it could be any help.