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 IdItem DescCalc FinalValueInv-1ApplesA+C+D Inv-2OrangesB+E Inv-3Bananaif ( D <5) then 0 else D IdValueA1B2C3D4E5 Thanks!!

Joining tables with column reference containing a formula

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!!

Python

Macros

Join

Accepted answers

AngelosPachis

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-parameter-to-the-formula-tool/td-p/367439

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

Screenshot 2020-12-01 064639.jpg

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

Screenshot 2020-12-01 065026.jpg

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.

Screenshot 2020-12-01 065319.jpg

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.

Screenshot 2020-12-01 065333.jpg

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.

Screenshot 2020-12-01 070109.jpg

Hope that helps,

Angelos

example_v2_batch.yxzp

All comments

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.

Screenshot 2020-12-01 062342.jpg

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

example.yxmd

AngelosPachis

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-parameter-to-the-formula-tool/td-p/367439

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

Screenshot 2020-12-01 064639.jpg

Screenshot 2020-12-01 065026.jpg

Two things to note here :

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.

Screenshot 2020-12-01 065319.jpg

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.

Screenshot 2020-12-01 065333.jpg

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

Screenshot 2020-12-01 070109.jpg

Hope that helps,

Angelos

example_v2_batch.yxzp

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.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335