Joining tables with column reference containing a formula

[Uddi]

Hi All,

I want to populate FinalValue column based on the calculation/rule from the Calc column. Can anyone give me some pointers on how to achieve this?

Inventory Table

Item Id	Item Desc	Calc	FinalValue
Inv-1	Apples	A+C+D
Inv-2	Oranges	B+E
Inv-3	Banana	if ( D <5) then 0 else D

Id	Value
A	1
B	2
C	3
D	4
E	5

Thanks!!

[AngelosPachis]

Hi @Uddi ,

One way to do that is to parse using a "Text to Columns" tool using the "+" sign as the delimiter and splitting into rows. Then with the "Find & Replace" tool, you can append the values for A,B,C and D and summarize to aggregate those based on the product ID. Last step is to include the if statement, and you can do that with a formula tool.

This workflow has some flows, for example if instead of having A+C-D, it wouldn't work cause the workflow can't pick up the "-" sign. 

Hope that helps,

Angelos

[AngelosPachis · Accepted solution]

Hi @Uddi ,

With a quick search in the community there is probably another more robust way to do it.... with a batch macro.

I drew inspiration from a post in the community here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Can-you-pass-an-expression-as-a-parame...

I first used a "Cross-tab" and an "Append Fields" tools to bring the table in the following format:

Then, I've created a Batch macro, which only contains a single formula tool updating the "FindValue" field with an expression. That expression can be altered by the control parameter, so whatever I've written in the formula tool is just a place holder for the expressions I will later feed in to this control parameter

Two things to note here :

1) Your mathematical expressions should have a correct format for Alteryx to work. So for example, in the "If statement" you have provided, an "endif" was missing which I have added to close the IF statement

2) Your batch macro should be configured as shown below : 

a)Your question should be set to the "Calc" field. That essentially tells the batch macro to feed in/replace the parameter with whatever is in you "Calc" field, so other expressions.

b) You should configure the batch macro to group by the "Item ID", so for each different Item ID in that field a separate "Calc" expression will be used.

Not sure how familiar you are with batch macros and if you are in the early stages of your Alteryx journey this might seem a bit overwhelming. I tried to explain each step with as much detail as possible, but feel free to reach out if you have any further questions.

This is a more robust solution than the one provided earlier and will probably work for every expression.

Hope that helps,

Angelos

[atcodedog05]

I was going to post a solution. But after seeing @AngelosPachis solution i changed my mind😅

@AngelosPachis has already done a really great job 🙂 especially by digging the posts and finding a dynamic solution. 

This is an amazing idea where you can pass the string to a macro and actually convert it to expression and perform operations.  This is definitely a great hack and skill up 😎

Thank you for this great new exposure @AngelosPachis 🙂

[Uddi]

@AngelosPachis Thanks for sharing the solution. This works really well.

I have a couple of questions in scaling up:

1. Each of the table has around 2000 rows. Will transposing and append impact performance?

2. Using macros to convert a string into a formula expression is fantastic. Ideally, this is the only clean solution I have found so far.

Is there any other way to convert a dynamic formula column (type string and includes various arithmetic operators ) into a formula expression?

Thanks again.

[AngelosPachis]

Hi @Uddi ,

1. If you are referring to the first table containing the ID and calc fields, then I reckon the "Cross-tab" and "Append Fields" won't impact performance significantly. If however you have 2000 distinct variables (A, B, C, D and so on) then I think that this will impact performance more. In either case, I believe that Alteryx will be able to handle it, but performance is something subjective and depends on the use-case.

2. I'm not aware of another solution to do this outside a macro at the moment. There are some tools such as dynamic replace that may be able to do it, but I don't have much experience in using them.

Let me know how scaling up worked for you and if the results you got after scaling up were still accurate.

Regards,

Angelos