how to bypass a union IF one stream is Null

[anthony]

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.

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.

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

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

 

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

[KaneG]

Hi Anthony,

The formal approach would I think involve wrapping from the join to the Union in a macro and then using a detour to decide what gets added to the union, however I can't think at the moment exactly how to change the detour direction based on row count... so, the quick fix is below.

It's a little bit of a dirty fix, but a quick work-around is to union on a 'dummy' row before the macro and then filter that off after the macro. It works in your example posted but in practice you would need to alter inside your Macro to make sure that dummy row does not get counted and processed.

Kane

[anthony]

Kane that does indeed work - but my download tool fails because I am not on the network - which is the other error I am looking to handle.  When API fails.

Any good way to ignore macro error when it fails in general - in this case I added a DL tool to the example to show variable union fixes issue but a new one is DL tool fail.

[KaneG]

Hi Anthony,

Basically, if the data goes to the download tool to try it, then it is going to error if it can't connect.

What you would need to do, is test the connection to see whether you want the data to go to that download tool. If the connection is dependent on you being on your companies network, then one way that you can do that is by using a Run Command to call ipconfig and parse the results looking for the 'Connection-specific DNS Suffix' or 'Default Gateway'. The Run Command tool would be set up like this:

Command: cmd.exe

Command Arguments: /c ipconfig > %temp%\e.1

Read Results: %temp%\e.1

You can then filter on that field to determine whether any data gets passed to the download tool.

Kane