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
danilang
19 - Altair
19 - Altair

Hi @chriscgmedia 

 

This formula contains extra quotes if the output is a numeric column

 

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

 

 

it should be 

 

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

 

 

Remove the single quotes around [req1]/[req2] and also the  0.  That would account for your type mismatch error. 

 

 

 

Can you Export(menu Options->Export Workflow) your workflow so the dynamic replace macro is included?    Also include the formulas for 2 or 3 KPIs exactly like you'd enter them into a formula tool, i.e  the CPM formula is  [Total Spend]/[Impression].   Some Input data would help as well. 

 

 

Dan

chriscgmedia
7 - Meteor

Thanks Dan, I am now getting a Parse Error 'tried to apply numeric operator to string value. So many steps!! 

 

The KPI formulas I am working with are below, as is the workflow. 

 

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

 

IIF(Contains([Req1],'Total Spend')&&Contains([Req2],'Active View: Viewable Impressions'), [req1]/([req2]/1000),0)

 

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

 

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

 

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

 

IIF(Contains([Req1],'Conversions')&&Contains([Req2],'Clicks'), [req1]/([req2],0)

danilang
19 - Altair
19 - Altair

Hi @chriscgmedia 

 

Your formulas shouldn't be based on the Req columns which define the columns required for the various KPIs, but on the data columns which contain the actual values you want to use in the calculation

 

the formula 

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

 

should actually be 

[Total Spend]/[Impressions]/1000  

 

since those are the columns which contain the data

 

Dan

chriscgmedia
7 - Meteor

@Danilang, Sorry thoroughly confused now!

 

I think I understand that I am trying to create these formulas to now work with the field names, however when I put just one in the Output Value of my formula (66) configuration I get 'Unknown variable "Total Spend" as this has not been seen before:

 

clipboard_image_0.png

 

That is why I had my IF statements in before based on the KPI value like this:

 

clipboard_image_1.png

 

This gets the formulas in my Output Value:

 

clipboard_image_2.png

 

However it throws an error up:

 

clipboard_image_3.png

 

I think this is because there are no new KPI fields created beforehand, it's trying to make something in a Field that doesn't exist!

 

 

 

 

 

 

 

danilang
19 - Altair
19 - Altair

Hi @chriscgmedia 

 

Here are the bits you need to make this work.  I took all the unnecessary tools and added them to the collapsed container just off screen on the right

 

w2.png

 

The data load and Data simplify containers are yours and I didn't make any changes.

 

In the Find Metrics container I added a formula field to the KPI text Input with formulas you provided for each KPI but referencing the data fields and corrected to avoid divide by zero errors like this

 

iif([Impressions]!=0,[Total Spend]/[Impressions]/1000,0)

 

I join back to the KPIs table to to select only the formulas that we know will work, based on the available columns in each run.  This joined output is fed into the Build fields... container.  The initial formula tool is yours minus the section to create the dynamic formula.  the data is split after this.  The Output fields container uses a Cross tab to build the output fields(double data type) and appends these to the main data set, again building only the fields required based on the input columns.  This is because the dynamic replace tool can't create output fields.  They have to already exist in the data stream.  The dynamic replace does all the work here, applying each of the formulas from the R input to all the rows in the D input.  Since we've narrowed down the possible formulas, based on the Input columns available, the process will always work even if columns are missing from the input.  

 

The result looks like this with one column per possible KPI based on the columns available in the input.

 

r2.png 

 I think that maybe you're missing a summarizing step because the KPIs are rather small.

 

 

Let me know what you think

 

Dan 

Labels