Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

Creating list of values from column contents


I need to build a dynamic formula box which works based on what columns my users have added to the data they have submitted. 


At present I have a workflow that produces a summary of what Integer columns are included in the data submitted, looking like this: 




I also have a number of KPIs I would like to show, but these need to be based on the user's data, so if Total Spend is missing, a KPI which relies on it is not shown. 




Could anyone help with a solution that might work here?! I've tried filtering, multi field formulas and a few others with no luck!






@chriscgmedia Do you want to use a conditional statement to create this logic?


if [Req 2]='Impressions' || [Req 2]='Conversions' then 'Clicks' else 'Total Spend' endif



Thanks for coming back to me Digan!


I need a new column with the KPIs that are possible based on the initial metrics loaded by the user. 


If the user only loads the Impressions and Clicks I would only be able to produce the KPI CTR, however if they also added Total Spend I would be able to produce CPM, CTR and CPC. 


Once I have these listed out I need to build a dynamic formula which will calculate the relevant KPIs. 






Hi @chriscgmedia 


Here's a workflow that answers the 1st part of your question, "Given a set of columns what KPIs can I calculate?"




The top input is your list of KPIs which gets transposed to put the 3 requirements into one column.  The bottom input is your data, which in my case has the columns Total Spend, Impressions and Conversions.  The Field Info tool extracts the list of columns from your data.  This is joined with the transposed KPI data on column name.  The summarize tool count the columns available for each possible KPI and the filter passes only the KPIs with 2 columns available.




I'll leave the second answer to you, but post back here if you run into any issues and we'll see if we can help some more.




P.S. I love Metadata workflows.

That has worked an absolute treat for the first section! Also the first time I've seen Field Info so that has also helped me with another workflow I was having issues with!


I'm going to try and get that second part sorted now. If anyone finds out how to use that to run a formula that would be helpful!




Check out the Dynamic Replace tool.  It has the ability to evaluate the contents of a field as a formula and calculate the results



Thanks Dan! I'm almost there but feel I've missed something...I'm getting the below error message:


Error: Dynamic Replace (67): Iteration #7: Tool #4: Parse Error at char(0): Formula: tried to apply numeric operator to string value


Do you know what this error is for? Feels like my Formula is wrong.






@daniland I'm not sure if I'm on the right path, I have found an article on Community which covers birth rates for this. 


I have tried to adapt that Output Value formula from:


" && tonumber([_CurrentField_]) >"
+tostring([Lower Limit])+




"IF [_CurrentField_] = "+[KPI]+" THEN "+[Req1]+"/ "+[Req2]+"ELSE Null() ENDIF"


Which gives me an output value as IF [_CurrentField_) = CPM THEN Total Spend / Impressions which does not seem to work. 


I've also tried a more simple:


IIF(Contains([Req1],'Total Spend')&&Contains([Req2],'Impressions'), '[req1]/[req2]','0')


To try and get one simple  [req1]/[req2] formula. 


I'm rather lost!


Just a guess, but try


"IF [_CurrentFieldName_] = '" +[KPI]+ "' THEN ["+[Req1]+"] / ["+[Req2]+"] ELSE Null() ENDIF"


For an example using _CurrentFieldName_ ..... Check out the Multi-Field Formula tool example.  On the left, under the Current Field tree, you'll see 3 options for _CurrentField_ and _CurrentFieldName_ and _CurrentFieldType_


I'm also guessing that your field names in the expression need to be enclosed in brackets.


Note the single quote followed by double quote, and double quote forllowed by single quote in this text:   '"+[KPI]+"'

This will grab the value from the KPI field and place it inside single quotes.


Thanks @ChrisTX


I'm not sure this is the process I need. 


I am looking to have columns dynamically brought into my data depending on what the user has inputted. 


So I would like something like this, based on the conditions I have set if certain Values are present then new columns are created with custom formulas:




The method I am trying at present seems to be blocked as I am not trying to replace my existing data with these formula options, I want to keep all existing data and add new data.