Unions with multiple inputs when some input can be missing

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.

Multiple Inputs.yxzp

Join

Accepted answers

mceleavey

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.

All comments

mceleavey

Hi @Ryan_Myers ,

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

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703