Dynamic input where file name changes

Hello,

I am trying to set up a process which imports data, manipulates it, and then appends it to an existing data set. The input however is from a file that is generated daily and saved with the as at business date in the title (i.e. 20170502obl.mdb). I have tried setting up a dynamic input but can't get it to work. I have used a formula to create a 'filepath' variable, which corresponds to the new file location. I don't get an error, instead the process just times out after 10minutes, where if you manually input the file it takes seconds.

Any help would be appreciated. Thanks,

Christian

Input

Accepted answers

NicoleJohnson

I'm wondering if it has to do with the way you're parsing/formatting the date in the two formula tools... I can't see what's happening in the second part of the first formula, so I'm not 100% sure this is the case, but if I was to take a guess, I'd say you're trying to format today's date (or yesterday's date?) into a string format that is then combined with the "obl.mdb" file name suffix. Looks like the issue might be stemming from trying to DateTimeFormat something in the second formula that Alteryx isn't recognizing as a date (based on however it is calculated/formatted in the first formula), which could be creating a file name that isn't being recognized by the Dynamic Input.

If that's indeed the case, then I would try using a single formula like the following to accomplish the new file name generation:

"C:\Users\bb2154\"+ToString(DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"days"),"%Y%m%d"))+"obl.mdb|obl1"

* Finds today's date, subtracts a day, formats it as yyyymmdd, then adds the filename prefix/suffix/tab name.

If the workflow was run using today's date (05/04/2017... May the Fourth be with you...), this formula would return a result of "C:\Users\bb2154\20170503obl.mdb|obl1", indicating a file saved with yesterday's date, which you could then feed into your Dynamic Input. Let me know if that was the right guess in solving your question?

Also, I've found in my own workflows that using UNC format for file references tends to work more consistently. (You can convert your file references to UNC after the fact by choosing Options > Advanced Options > Workflow Dependencies > All UNC... though you'll want to make sure any text you use in the formula above uses UNC format as well.)

Thanks!

Nicole

All comments

bsharbo

Hello. Would you be able to export your workflow and attach it.

Looking at your workflow I don't see anything immediately incorrect (i've used a very similar setup to do the exact same thing you are doing), so i'd like to try to run your workflow on my machine and see if the error is repeatable (and try to help) :-)

You can make up the data in your file so that you are not sharing any private info.

NicoleJohnson

If that's indeed the case, then I would try using a single formula like the following to accomplish the new file name generation:

"C:\Users\bb2154\"+ToString(DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"days"),"%Y%m%d"))+"obl.mdb|obl1"

* Finds today's date, subtracts a day, formats it as yyyymmdd, then adds the filename prefix/suffix/tab name.

Thanks!

Nicole

Beek

Thanks for the help. I am using the first formula to account for weekends (i.e. pick up friday's data set when run on monday). It looks as though my process is fine - the file is just so big that it is causing issues. If I set up a similar, smaller dummy dataset, it runs quickly and accurately.

Is there a way to add SQL filtering during the dynamic input to reduce the size of the data set as it is being imported?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703