How to dynamically rename field name based on year

[ruangkhaw]

Hi All,

I have to union hundred of files in a directory. My problem is : each file has no column names that specify the year of each field, for example some files started a column with "FY 22" for April Fiscal year 2022 then the next column is "F12" for May Fiscal 2022 then "F13" for Jun Fiscal 2022 so on and so forth.

While some of the files starting the column with "FY 21" for April Fiscal Year 2021 and so on and so forth.

My plan is to create a batch macro for dynamic rename using IF THEN ELSE expression as picture below

However, I run to a road block where when the file doesnt have a column that start with "FY 21" then it will match the wrong Fiscal year.

Is it possible to write a formula where IF  "FY 21" then "F12" = May FY 2021 and "F13" = Jun FY 2021 if NOT "F12" concatenate with "FY 22" etc.

Any thoughts on how I can union all these hundred of files without matching the wrong columns?

Sorry I cant share the file because it's in my work directory.

Thanks!

[Qiu]

@ruangkhaw 
First of all Union of hundreds files does not seem to be right. 😁
I believe persons here can help you with a better solution if you could share a few sample input files.
Then we will also get on with the dynamic Renaming problem.

[JarekSkudrzyk]

@ruangkhaw 

I think you can use dynamic input tool with filename attached, bring in all data and only then produce a table output you want.
Please see attached sample solution.
As @Qiu wrote - please share some sample files so we can better understand your problem and help find the solution.

[JarekSkudrzyk]

@ruangkhaw you can modify the filenames before "Cross Tab" tool to get the column names in desired format (e.g. unified).

[ruangkhaw]

Hi @Qiu thank you for replying. I attached the my sample workflow by  creating 3 file in a directory and have a batch macro iterate all file by stacking them up together(like Union method without using a union tool) which I have done so with hundred of files in a single directory as long as those files are in the same format.

[ruangkhaw]

@JarekSkudrzyk thank you for your reply. Im not able to open your file. however, I attached a sample workflow and I think the problem I am having is in a batch macro where it iterate all files in a directory and stack them up together. In the meantime, in the same batch macro I added the dynamic rename tool, thats when it gets tricky because the IF THEN ELSE statement that I have would apply to all fields.

if you open those 3 files in the directory you will see that each file have different start year and that when I get stuck.

I dont know how to upload a directory on this thread so I uploaded 3 dataset files on there call :ID 0012, ID 0013 , ID 0014

which you may have to save those 3 files in a folder and manually added to directory path.

[JarekSkudrzyk · Accepted solution]

@ruangkhaw I have amended your macro and workflow - it should now work fine. Please let me know if it does not.

[JarekSkudrzyk · Accepted solution]

@ruangkhaw probably you could not open my file because I have added the directory with inputs to .yxzp file. I think in such a case after importing you need to manually run the .yxmd file from folder where the .yxzp was unzipped.
But this is not important now:) please take a look at my answer above

cheers

[ruangkhaw]

@jarekSkudrzyk Omg it works beautifully! Thank you so much!!

[ruangkhaw]

@jarekSkudrzyk Omg it works beautifully! Thank you so much!!