This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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:
* 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 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?
Ah, gotcha. If you're just looking to limit the number of rows brought in, I believe you can specify a Record Limit in your Input Data Source Template file selection (see window below, Record Limit value). Record Limit will be passed through to all the files running through the Dynamic Input. And/or you can modify the SQL statement in the Table or Query field in that same template configuration window if you only need specific records/fields from each file (rather than the blanket "SELECT * FROM __" query).