Combining rows

[rdeshpande]

Hello

I am trying to create visual of movement of files across directories. The files are moved from one directory to another as they are worked on. My workflow is giving the following output

FileName	Folder_1	Folder_2	Folder_3	Todays date
file 1.pdf	3/14/2024 16:08			3/14/2024
file 1.pdf		3/15/2024 12:09		3/15/2024
file 1.pdf		3/15/2024 12:09		3/15/2024
file 1.pdf			3/18/2024 8:38	3/18/2024
file 2.pdf		3/14/2024 16:00		3/14/2024
file 2.pdf			3/15/2024 12:01	3/15/2024
file 2.pdf			3/15/2024 12:01	3/15/2024
file 2.pdf			3/15/2024 12:01	3/18/2024

My desired out put is

FileName	Folder_1	Folder_2	Folder_3	Todays date
file 1.pdf	3/14/2024 16:08	3/15/2024 12:09	3/18/2024 8:38	3/18/2024
file 2.pdf		3/14/2024 16:00	3/15/2024 12:01	3/18/2024

Is this possible? Thanks in advance

[SPetrie]

Transpose your data using Filename as a key column. Then use a filter tool to remove any rows with empty values.

Next, use a cross tab grouping by file name with the "Name" field for column headers and the "Value" field as the values and Last for your aggregation method.

You can use a select tool and sort to get the rows and columns in the order you want after that.

[rzdodson]

@rdeshpande attaching a mock-up of the workflow that @SPetrie suggested. The note I would make is adding in a Dynamic Rename to remove underscores from your headers. The Cross-Tab tool always includes underscores for all fields that had some aggregation performed on them - this is an easy way to remove those without having to do it manually for your fields with a Select tool each time a new field gets added to the input.

[cjaneczko · Accepted solution]

This should do it.