Dynamic input where file name changes

[Beek]

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

[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 · Accepted solution]

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

[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?

[NicoleJohnson]

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

On a related note, looks like someone had a similar suggestion in the Community Ideas page, regarding record limits set in overall Workflow Configuration also applying to Dynamic Input tools, if you wanted to cast your vote for that one... https://community.alteryx.com/t5/Alteryx-Product-Ideas/Record-Limit-for-All-Inputs-Extend-Functional...

Hope that helps? :)

[SeanAdams]

Hey @Beek,

There's perhaps an easier way to do this:

- Drop on a directory control.   That will allow you to find all the files that match a certain pattern, even if in subdirectories

        - The benefit of doing this is that you don't need to worry about creating a filename that doesn't exist

- From the directory control you'll get a list of filenames.   Take the most recent of these (or the one that best matches your need)

- pump that into the dynamic Input with a record-limit for testing (until you're sure that it's working) per @NicoleJohnson 's suggestion

I put together a similar version of this today, for a piece of work that I'm doing with @MattD - I've attached the workflow below so that you can see how it works.

Let me know if that gets you to a solution you can work with?

Cheers

Sean

[SeanAdams]

Hey @Beek - just checking in - did any of these get you to a solution that helped to close this out?

If so - would you mind marking this thread as solved?

Many thanks

Sean

[nazuk]

@SeanAdams : i am also stuck in similar situation. So my file extension is.xlsx. when i am following exact trick i am getting an error as no sheet specified, you must apecify a sheet.

My problem is:

I have two file in a folder.so one file ll always exist. And the second one may or may not exist.the second file has a dynamic name. So in my case if second file exist too, then i hve to union both.

Please suggest.!