Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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

 

 

 

7 REPLIES 7
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
18 - Pollux

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
18 - Pollux

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
18 - Pollux

Great to hear!

Alteryx_new
6 - Meteoroid

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.

Labels