Filename From Field for A Million Rows

[ricoo]

Hi All,

Is there an alternative to dynamically set the path/filename of an output file in Alteryx aside from using the option “Take File/Table Name from Field”?

 

Let’s say my data set contains 5 million records. If the path/filename is contained in a field, then the path/filename should exist in all 5 million rows. It’s going to affect the performance of running that workflow.

 

[BS_THE_ANALYST · Accepted solution]

@ricoo 

Seems like you should use a batch macro with 1 iteration. The purpose would be to dynamically overwrite the name of the file using just 1 cell's worth of information. This way you will also retain the dynamic element. Hopefully this makes sense, if not, I'll mock up an example. In my mind, you use a summarize tool on the "field" with the file name to collapse into one row. This feeds into the batch macro. You drop this field from the original data.

All the best,

BS

[DataNath · Accepted solution]

Hey @ricoo, Alteryx ought to be able to handle 5m rows just fine - adding a simple extra field like a FileName shouldn't degrade performance much. If you're seeing this it's likely to be from elsewhere in the flow.

If you're insistent on not having this extra field, you could use a batch macro whereby you Sample 1 record to use to build the FileName and feed this into the Control Parameter linked up to your Output Data tool (changing the output file location). Then send all of your data through the main input of the macro.

[caltang · Accepted solution]

As above, I don't think that will affect performance all that much. I've tried that method with 44 million rows of data cumulatively and it worked well... 

[ricoo]

Hi @BS_THE_ANALYST , sorry I can't picture out how this would be done. 

[ricoo]

Hi @DataNath , Thanks! Alright I'm gonna try this and will update on a separate response .

[ricoo]

Hi @caltang . Thanks, that sounds good and reassuring. But I would still like to try the above solutions. 

[ricoo]

Hi All - @BS_THE_ANALYST , @DataNath , @caltang  thank you for taking time to respond to my question. These are all helpful. I just had a meeting with my team, and we've arrived to a conclusion that having more than 5 million records is highly unlikely. So all your responses guaranteed our use case. I'll accept all your responses as a solution. Thanks All!

[BS_THE_ANALYST · Accepted solution]

@ricoo, just for your understanding I put together a dummy workflow. The output tool (within the macro) is outputting to a yxdb. You can change the file extension in the macro. However, we're talking about 5 million rows. Don't use excel, unless you're trying to generate a workflow that hits the sheet limit in excel then moves onto the next sheet (but that's a different story).

All the best,

BS

[ricoo]

Hi @BS_THE_ANALYST thanks for doing and providing the solution. I really appreciate it. This is still very helpful, although as I've previously said that we might not reach 5m rows but this still comes handy. 

I guess the key here is to use the Action Tool to update the filename then. That's cool!