how to bypass a union IF one stream is Null

Hi, I am trying to figure out how to easily bypass a Union if one stream is empty/null/na or if API in macro is not available. I have run into this a few times now and have not found a easy way to get past this issue. I think I could probably use a macro with a set of detours/control parameters?? but that seems like major overkill.

bypas

Here is what I am trying to accomplish- take my list of skus - pass to a join and join skus to the data from the lookup table. For all skus in the lookup, join then pass through. If skus not in lookup via left join - then use the macro to call an API to get the missing data - that data is then written to lookup table(in the macro) for future reference so I am not hitting api unless a new sku(s) is added.

If the api is not available or all skus are in the lookup table (eg no left join), then the union does not pass along the good stream to complete the workflow.

Anyone have a clever approach to get this process to work as intended?

Thanks

Anthony

FYI - high level of macro - basic input - get data - output and write to table new data.

macro

Macros

Common Use Cases

Join

Accepted answers

anthony

Solved? Yes if you use quotes.

Here is what I learned: use dummy data and filter off downstream to pass through something, use a dummy output like below in macro so that it keeps processing down stream.

I was able to hack my way around my problem by using above macro to write any new data to yxdb and then

pick up later in the stream as needed. I have the dummy output that is doing nothing other than stopping union from moving forward as it waits for macro.

If macro fails it keeps moving forward and if macro gets new data it updates my lookup table to append to data stream on join over.

"solved"

on to new problems

anthony

Thought I would post a follow up to something I thought that would work and did - using a detour/control parameter in the macro if passing null to macro.

So now i check the input with a filter and if null it updates the detour control so that an error is not generated and also pass the data stream to the macro input.

All comments

chris_love

Can you not do a count from the data coming from the API, append that count
to the unioned data and then filter out a count of zero? (then drop the
field)

Chris

scottj

Anthony,

You could use a Filter before the union set to !isnull(variable). This will still have the field for the union, but not the data, so the union just passed the one set. See attached. You could also do a check for nulls with the Summarize tool, the test the count to check the process.

NullUnions.yxmd

anthony

Ok, i tried that and it seems to be a issue with the macro erroring - something is not getting passed to filter

How can I get the macro to pass something so this approach works. The error is "Error: APICatalog (7): All fields must be mapped for the input: "Input17""

Input 17 is the macro input.

scottj

Select the macro. In the configuration, choose the correct corresponding fields in the dropdowns.

anthony

This has turned into - how do i bypass the macro that is erroring because it is missing data -

I have updated the package to show the issue - macro expects a variable and fails and filter does not work because macro output fails at union it appears.

NullUnions.yxzp

anthony

Chris there is nothing coming out of the macro to count - after futher investigation it is the macro erroring that is causing the issue at the union.

This error is not giving the output anything to work with down stream.

Any idea how to deal with a failing macro?

pmiecz

Another way to bypass union is to use "Detour End" tool. It will allow data flowing even though one input is null or empty.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703