Using two inputs in a formula tool - Help!

[Metabrit]

Hopefully someone on here can help me.

I have two files. One of which contains numerical data that needs to be assessed against certain limits. The other contains values that need to be added to the data and limits that the data needs to be assessed against. These files are in different formats and I do NOT want them to be joined or combined in any way, they need to be separate files. I have run them through Alteryx to clean up values, add and remove columns ect.

Originally I thought you could simply use the formula tool to reference another file or sheet like Excel can easily do but sadly have not found a way to do this. I've tried using a batch macro but that just joins the two sheets together which is not what I want. To demonstrate what I'm trying to achieve more clearly...

e.g. We have a value of 3.567 in spreadsheet 1. We need to add values of 0.34 and 0.01 to the data, from spreadsheet 2, to get 3.917 and compare that to a limit of 4.0. The result will then be a pass.

An added complication is that the values to add and the limits to assess against depend on 3 other fields, common to both files. One to identify the model, and the other two to identify the type of limits. Essentially the formula will be....

If field 1 in spreadsheet 1 = field 1 in spreadsheet 2 AND
if field 2 in spreadsheet 1 = field 2 in spreadsheet 2 AND
if field 3 in spreadsheet 1 = field 3 in spreadsheet 2 THEN
add value X and value Y from spreadsheet 2 to value Z in spreadsheet 1 AND IF
new value Z is less than limit X from spreadsheet 2 THEN enter "Pass" in field "Pass/Fail/Provisional" ELSE IF

new value Z is more than limit X from spreadsheet 2 THEN enter "Fail" in field "Pass/Fail/Provisional" END IF

Is there any way to do this in Alteryx or should I take this part of my task externally? E.g. excel or another tool.

These files will later be put into a database on a website so I'm wondering if it would be better to do this in java or some other coding when it is part of said website.

[jdunkerley79]

This really feels like a case where you would join the data together but if not then the only approach I can think of is to dynamically generate a formula and then call the Dynamic formula tool in the CReW macros by @AdamR_AYX

If you can post a sample table of both might be easier to put a concrete example together

[Metabrit]

I've downloaded the macro pack from AdamR and will give the dynamic formula tool a go. Here's an attachment to demonstrate what I'm talking about, its pretty oversimplified compared to the real thing.

Essentially three fields will be selectable.

1. The model field will be selected from a drop down list of models

2. The assessment field will be selected from a drop down that depends on model

3. The stage field will be selected from a drop down that depends on model and assessment fields.

This will then change the values that go into the calculations so that you are able to compare the data to any applicable limits and see if you pass or fail.

[Metabrit]

Hi, I'm still having issues figuring this one out. The dynamic formula tool doesn't seem to be the right tool. Any ideas?

[MarqueeCrew · Accepted solution]

Here's a possible solution....

Use a join just for the temporary calculations.

Thanks,

Mark