This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
What I've done is use Directory Tool -> Dynamic input to read my data in (you'll likely have to make the directory tool look for more specific files, or add a filtering step before the Dynamic input/parse tool.
I then create a target column, with the name current year, so I can match to the transposed columns of the input file. Once I make this match, I use the multi-row formula to rename them. I've created a duplicate of the Name column here, but I don't think it's necessary in general, I just wanted to show that the numbers were generated correctly.
For this it is important that the "Values for Rows that don't Exist" is set to NULL, since I use a Null check to set Prior Year correctly.
After removing unnecessary information, I CrossTab back into the original format.
Let me know if you have any questions, or this workflow doesn't work for you for whatever reason.
I think you may still be able to do something like what I've done above, you'll just need to use the GroupBy feature and group by Filename.
Instead of using the find/replace Tool, I'm just joining in the results of my Regex before the Transpose so I can group by year, then my formula just creates that same column that the previous find replace did, replacing Current Year with the year, and setting all the others to the Name column. The multi-row tool is unchanged, and the CrossTab just needs to be set to GroupBy the RegExOut field as well.
I've attached an updated workflow, let me know if this helps.
I've just created some files with your naming scheme filled with 1s. as the data.
If you can add some mock data of what an input to this process would look like, I could put together a more realistic example workflow.
When you say it has dozens of fields, are you saying that there is like Year+12, or you're saying that there are multiple fields that need to be transformed like this?
i.e. you'd have:
If so, I think you'll want to find a way to get this field name into the workflow as a field, and group by it as I did with the dates. I can help you out if I can see a more realistic example of the data.
I had the join tool on Record Position because there is one date I want to add per file. so the first file corresponds with the first date, the second with the second, etc.
The reason it's spitting out the 29+ is because there are multiple rows for each input, obviously something I should have accounted for in my workflow.
Are you able to get any sort of identifier for the file that a row comes from? This could be used in conjunction with the RecordID tool to join that identifier on RecordID instead of by position.
Is the number of rows in each file the same every time? If so, you could use the RecordID tool and a Formula tool to change it so that the first, lets say 10 rows are assigned to 1, the next 10 are assigned to 2, etc. Then, join on the RecordID from the RegEx.
The formula you'd want here is: CEIL([RecordID]/<numberofrowsperrecord>)
The attached workflow implements this, but again this will only work if the number of rows per file is constant across all files.
I can input all the files together in one step using *.zzz.
File Name aside, there is a string field called Calendar Year. It's just the 4 digit year.
So, I can get all the files into the data stream in one fell swoop.
This is still dummy data. But this is essentially what the data looks like going into the two (2) Select tools before the Union. (The workflow was originally developed to accept 1 year. In that case, PY, CY, Yr1... are annually changed using the Select tools.)
It seems like calendar year isn't quite a unique identifier for the file, since the 2015 file has both 2018 and 2015.
If the calendar year 20## was always the same as the cy##, then, you could create the Calendar year from your RegEx output by adding the string "20" in front.
However, in this case I'm not sure you really need to do anything with the file name, so I must be missing something. You could simply do the transposing, renaming, and cross-tabbing using the Calendar Year field as the Grouping Field.
I'm sure I've missed something here so please let me know what it is,