Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Creating list of values from column contents

chriscgmedia
7 - Meteor

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: 

 

clipboard_image_0.png

 

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. 

 

clipboard_image_1.png

 

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

 

Thanks, 

 

Chris

14 REPLIES 14
DiganP
Alteryx Alumni (Retired)

@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

 alteryx_logo.png 

Digan
Alteryx
chriscgmedia
7 - Meteor

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. 

 

Thanks, 

 

Chris

danilang
19 - Altair
19 - Altair

Hi @chriscgmedia 

 

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

 

w.png

 

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.

 

r.png

 

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.

 

Dan

 

P.S. I love Metadata workflows.

chriscgmedia
7 - Meteor

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!

danilang
19 - Altair
19 - Altair

@chriscgmedia 

 

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

 

Dan

chriscgmedia
7 - Meteor

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.

 

 

 

 

 

chriscgmedia
7 - Meteor

@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:

 

"IIF(tonumber([_CurrentField_])<="
+tostring([Cutoff])+
" && tonumber([_CurrentField_]) >"
+tostring([Lower Limit])+
',"'
+[Bucket]+
'",[_CurrentField_])'

 

TO:

 

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

ChrisTX
15 - Aurora

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.

 

chriscgmedia
7 - Meteor

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:

 

clipboard_image_0.png

 

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. 

 

Thanks, 

 

Chris

Labels