Transpose + Summarize + Join

[suby]

All,

My workflow goes like this first transpose then summarize and Join on common fields.

I'm reading in the sheet 1 TO Sheet 5 using a batch Macro then I'm transposing

When the Macro Runs

when sheet 1 is read and transpose using Record ID as Key column and Data Columns as ID and FOLDER
when sheet 2 is read again it works ok when transpose using Record ID as Key column and Data Columns as ID and FOLDER
when sheet 3 is read again it works ok when transpose using Record ID as Key column and Data Columns as ID and FOLDER

but

when sheet 4 is read again I'm not getting any errors on my Transpose tool but i get an error on the Summarize tool
because the summarize tool is configured using ID and FOLDER which are common for sheet 1 to sheet 3 which runs ok but not for sheet 4 or sheet 5.

I'm looking for a dynamic solution where

sheet 4 column header M_ID Should be dynamically renamed to ID and FILE ID should be renamed to FOLDER.
sheet 5 column header F_ID Should be dynamically renamed to ID and FILE ID should be renamed to FOLDER.

So that all my transpose , summarize and Join will continue to run through.

Thanks

[binuacs]

@suby One of the option I am thinking of keep the column heading in a separate file and update the field heading using the Dynamic Rename tool

[suby]

I got your point but I'm facing an issue with output record sets which means.

Note - Removed the Record ID column to avoid  some confusion final sample data attached.

When i used the Dynamic Rename before my transpose it works ok for the first two sheets because the column names ID and FOLDER are in same position.

Issue - when using the Dynamic rename using (Positional Rename) 

- when the macro reads the sheet 3 it renames the description field as FOLDER and creates an additional column called FOLDER 2.

- On sheet 4 the same issue the M_ID  and FILE_ID  is  on different position  compared to sheet 1 and sheet 2

- On sheet 5 the same issue the F_ID   and FILE_ID  is  on different position  compared to sheet 1 and sheet 2.

How to handle this when the columns are not in same order across sheets.

Thanks.

[suby]

Sorry Missed the Latest sample file.

[grazitti_sapna]

Hi @suby, the schema of the first two sheets is the same and there is a difference in the schema of sheet 3,4,5 therefore, I have used a filter tool to transform these sheets individually. This will only work in case you are using the same schema as shared in your sample file.

After this you can apply your transform, summarize tool as per the requirement.

I hope this helps!

Thanks!

[DavidP]

Hi @suby 

Why don't you include the Transpose tool inside your batch macro. You'll  need to also extract the Sheet Name as an identifier so that the output of the batch macro looks like this:

You can then use a simple if statement in a formula tool to rename the values in the Name field before the Summarize tool.

if [Name] IN ("M_ID","F_ID") then "ID"
elseif [Name] = "FILE ID" then "FOLDER"
else [Name] endif

The result would be something like this:

Attached is the mock up workflow.

[Kurohits]

Hi @suby 

I have used Dynamic rename tool to dynamically search the field names and look for the specific keywords and then rename those fields to desire field name. One thing I noticed that In Sheet 1,2, and 3 there are some leading and trailing whitespaces in the "ID" and "FOLDER" fields. I don't know if it exists in the original data or you intentionally added to the sample data. Please double check your original data. 

I have attaching the workflow for your reference. Let me know if this solves your purpose. 

Thanks 

Rohit Gupta

[DavidP]

This is how it would work with a batch macro

[suby]

Thanks David much appreciated.

Many thanks to you, Kurohits, & Sapna..