ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

How to add record count to name of the output excel spreadsheet

harsha_rappan
7 - Meteor

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

 

 

 

CharlieS
17 - Castor
17 - Castor

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?

apathetichell
11 - Bolide

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...

 

 

harsha_rappan
7 - Meteor

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"

 

apathetichell
11 - Bolide

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)

 

 

harsha_rappan
7 - Meteor

It worked like a charm, thank you so very much for the help, truly appreciate it

apathetichell
11 - Bolide

Great to hear!

Labels