One of business users wants us to add the total number of records(count) as part of the file name along with the date.
I managed to add the date to the file name, can you please help me to add the count of records to the file name.
The file that we share or the output file is an excel spreadsheet
Here is an example:
Filename : Conventional Vendor Receiving Report
Date: 04-08-2021
Total number of records: 65000
Here is how the output file name should look like:
Conventional Vendor Receiving Report_20210408-65000
Just to be very clear, the date and the count of records should be part of the FILENAME NOT as a new column added inside the file.
Any help is truly appreciated
Regards,
Harsha Rappan
Solved! Go to Solution.
Nice work on getting the date in there. If you've got that sorted out, adding the record count should be a snap.
The key here is the Count Records tool in the Transform category. This tool will output a field called [Count] with the count of records as the value.
https://help.alteryx.com/current/designer/count-records-tool
Add this tool to your workflow to create that value, then use an Append tool to get that value appended to the data where you write the file name. Does that make sense?
you can concatenate the string of the date column (ie something like tostring([date])+"-"+tostring([count[) you presently use for filename with an appended count record column.
Then on your output option you can use that field for "take file/table name from field" and make sure to DESELECT the check box which says "keep field in output" - and then the field will not be part of the output...
1. I added the record count tool and the append tool.
2. I added the formula tool, added a new column and named it filename
3. I entered this formula to add the date to the filename and here is the formula
4. "\\filename"+"_"+DateTimeFormat(DateTimeNow(),"%m%d%y")+".xlsx|||Sheet1"
5. After adding this, in the output tool I checked the Take file/Table Name from the field
6. I chose Change the entire file path
7. I chose filename from the field containing file name or part of file name
8. I unchecked the check box of keep the field in output
How do I add the count to the file name? I tried adding the count column into the formula tool, it did not work, here is what I tried
"\\svuent.supervalu.com\groupdata\General\Conventional Vendor Receiving Report\Conventional Vendor Receiving Report"+"_"+DateTimeFormat(DateTimeNow(),"%m%d%y")+"[Count]"+".xlsx|||Sheet1"
assuming you've appended [count] as a field - you need to convert it to string with tostring([count]) otherwise it will give you an error.
So the steps would be:
1) count records tool.
2) append count to your data.
3) in your formula do exactly what you did but use tostring([count]). (or whatever your record count column is called)
It worked like a charm, thank you so very much for the help, truly appreciate it
Great to hear!
Hi,
I have the same requirement and when I followed this post and tried the same it works well except it also creates a count field in the output
My approach is
1)input count records and data input to the append fields , which generated extra field in the file which is count
2)output the append fields to the filter tool
3)derive the formula to generate the "filepath"
4)then output the filter tool to the excel output , selected take file/table name field , selected change entire file path ,selected the field containing filename which is "filepath" and deselected keep in field in output which is "filepath"
Unless we use a select tool between Filter tool and Output tool the count field appears in the output.
I am wondering how it worked with out select tool in the post mentioned.