Unions with multiple inputs when some input can be missing

[Ryan_Myers]

I have a real simple question that i can't seem to find the answer to.  I have several inputs and each input goes through its own transformation to get the data in the same format then the files are Unioned to get one output.  The problem is that sometime some of the inputs are missing and that prevents the workflow from running.  How can i build something that will return a message in the output that says that file was missing (while still returning the results for all the other files.  

I have attached a real simple workflow.  2 of the inputs exists but the third is missing.  In my main workflow there are 20 plus files and the file format is different for each which means there are different transformation steps between the input and the Union.  

[mceleavey]

Hi @Ryan_Myers ,

are all input files the same? as in, the same file but monthly, for example?

M.

[Ryan_Myers]

@mceleavey -Each input file will be different from the other input file (but not in the example workflow i posted) and each has to go through its own unique transformation.  Some files are daily, some are weekly, some are monthly which is why some are not always available when the workflow is run.  

[mceleavey]

Hi @Ryan_Myers ,

the easiest way to do this is to use the Directory tool to look into the folder containing each file. If there are no files there then it returns nothing, but doesn't error. 

If it's a case of loading in the latest of each file then you sort by creation time and sample the first 1 file. You can then join this to a list of files you've already loaded and take the file that doesn't match.

This allows you to return blanks without returning an error on a missing file.

M.

[Ryan_Myers]

@mceleavey -I tried that initially as that is the solution that comes up in a lot of searches, but i don't see how that helps in my situation.  I would need to use the Directory tool for each individual input (along with a dynamic input tool i assume).  Replacing the standard input with the Directory Tool/Dynamic Input still puts me in the same spot where there is no data and the Union fails.  

[mceleavey · Accepted solution]

Hi @Ryan_Myers ,

that works for me so I'm not sure of the problem:

Can you post a mock up of what you're trying to do because I'm obviously just misunderstanding the issue.

One thing you can do is create a mock directory to the template of each file, load this in then filter it out by filename. This means you will always load in one record as the template, then remove it. If it's the only file (in other words there no new files) then it will be there as a record, go through the data fixes and unions, then be removed. You can do this by sampling the latest file before doing the data cleansing and prep, which would would perform it on the template file or the new file if one exists.

M.

[Ryan_Myers]

@mceleavey -Can you package that workflow up, i want to compare the configuration with what i have.  I must be missing something. 

[mceleavey]

Yeah sure. Attached.

If you're struggling, DM me, I'm happy to jump on a Teams call to help you out.

M.

[Ryan_Myers]

@mceleavey -Yeah, i still have the same issue.  I took your workflow and connect my inputs to the three files.  Then i moved that third file to another folder to simulate when the file is missing.  They dynamic input fails and no data comes out of my union.  

[mceleavey]

Hi @Ryan_Myers ,

Can you show me the error? 

Make sure the template is always pointing at a file that exists.

M.