Data Formatting in a Specific Pattern

I have 2 Input folders "Raw Interim and Raw Rollforward" which has multiple files inside it. Only the "830038_RPS Daily Transaction Report.xlsx" files are to be considered for Input.

The output need to be in the exact Pattern as Provided in the "Tested Interim and Tested Rollforward" folder.

Note:

Attribute A comment box needs to be added where everything will be constant except the name of the person who signed off. This person’s name will be entered via interface by user and needs to be updated dynamically

The above comment will only be added when the last row has the signed off date.

Also the signature needs to be replicated in the output sheets as well.

Tested files_Rollforward_2023.zip

Tested files_Interim_2023.zip

Raw Files Interim.zip

Raw Files Rollforward.zip

User Input.png

Reporting

Accepted answers

All comments

DataPirate26

@binu_acs Would be glad if you can help out on this. Its a bit complex one.

binu_acs

@DataPirate26 it would required more effort, have you tried any thing from your side, also i dont think comment box can be added in the excel output using Alteryx, also i noticed that the input and the output are almost same, what exactly is the requirement here?

apathetichell

This isn't doable. You need Python. In addition to comments:

this:

Also the signature needs to be replicated in the output sheets as well.

is not possible witha template file. It's possible with Render - but Render will overwrite everything and you'll lose your formatting. Go back to Python. I'm not 100% sure this is going ot be worth the effort.

DataPirate26

@binu_acs I tried using reporting tools probably i feel it is not possible only using alteryx tools. Yes the difference is the 3 lines added in row 2,3, and 4. Let me know if any script can be used to achieve it.

C261576.1_-_RPS Daily Transaction Report_01-06-2023

PBC : Gick Travis, Consultant, Technical Support ( Need to be provided by the user and should come dynamically)

Source : RPS

Also the comment box will be constant in each of the file in that particular range of cells except Signature ( highlighted part attached in screenshot) needs to be dynamically populated. Both PBC values and Signature needs to be provided using an interface tool.

Highlighted Part.png

DataPirate26

@apathetichell Yes I guess the Signature can't be read in Alteryx as it is reflecting that particular cell as blank. So maybe we need some other Alternative. I am not sure how can we Integrate Python in Alteryx. Let me know if there is any approach which you can provide

DataPirate26

@binu_acs If you see The Input File Name is "830038_RPS Daily Transaction Report.xlsx followed by the date". Where as the Name of the Output Files are

C261576.1_-_RPS Daily Transaction Report followed by the date. Also the below attached screenshot Data needs to be populated dynamically in each of the files exactly in that same range.

There are 2 folder Raw Interim and Raw Rollforward. Based on our folder selection the Output Folder will be Tested Interim and Tested Rollforward which also needs to be created dynamically.

Add on.png

DataPirate26

@binu_acs Any approach you can guide with on this. Even if something we can achieve partially

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703