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!

Thanks,

Chris

Dynamic Processing

Interface Tools

Data Investigation

Iterative Macro

Accepted answers

danilang

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

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.

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

Let me know what you think

Dan

TheMAP_DynamicWorkflow v2.yxzp

All comments

DiganP

@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

chriscgmedia

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703