I'm looking at a large volume of data where there are multiple (more than) 40 columns for client benefits that are not all to be included in a calculation, e.g:
Client | Ben1 | Ben1_Inc | Ben2 | Ben2_Inc | Ben3 | Ben3_Inc | Ben# | Ben#_Inc |
x | 1000 | Y | 1100 | Y | 1200 | N | ||
y | 1200 | N | 1500 | Y |
If a benefit is only to be included in the calculation when the name of the subsequent column is the column name with a "_Inc" suffix
It would be possible to achieve this using a formula tool with a formula for each specific benefit column with the syntax
Output column [Ben1]
IF [Ben1_Inc] = "Y" THEN
[Ben1]
ELSE
0
IF
However, as each of the benefit columns has a different name (i,e. not nicely named Ben#), this will be a very tedious process, as all (more than 40) formula will have to be manually constucted.
Is there a way to reference multiple "generic" / "dynamic" columns in a formula, e.g.
Output column [column]
If ([column] + "_Inc") ="Y" THEN
[column]
ELSE
0
END IF
So if we're in column number X and the name of column number X+1 has the "_Inc" suffix then change the data in column X based on the data in column X+1...
Thanks in advance!
Solved! Go to Solution.
Hi @Paul_s_Moody ,
You can accomplish something like this by first transposing your columns to rows, then using a multi-row formula to perform the functions you described. You can then use a cross-tab tool to get the data back into its original format. I've attached a sample workflow that demonstrates this.
I hope this helps!
Thanks,
Wes
Take a look at using the transpose tool to stack the fields on top of each other. You can choose to transpose by client, that way you can put the data back to normal with a cross tab tool.
You can then use the Multi-Row Formula tool to look at the rows prior to or after the field that contains the Y or N. So your formula would be something like IF [value]=Y then [row-1] else 0 endif
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Transpose/ta-p/89741
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Cross-Tab/ta-p/4368
You also hav samples of these tool in designer to see how they work.
Hope that helps
Hi Wes,
Many thanks for your reply, the workflow is definitely a starting point, but I don't think I'd explained the issue well enough.
The table below shows what I'm expecting the data to be transformed into:
Client | Ben1 | Ben1_Inc | Ben2 | Ben2_Inc | Ben3 | Ben3_Inc | Ben# | Ben#_Inc |
x | 1000 | Y | 1100 | Y | 0 | N | ||
y | 0 | N | 1500 | Y |
I'm using the _Inc columns to change the data in the prior one so because of the N in the relevant _Inc column, the value in the benefit column has been set to zero (marked in red).
The code in the workflow you send changed the values in the _Inc columns rather than the benefit columns.
I'm trying to adjust the code appropriately, but as yet I've not found a suitable formula..
Many thanks,
Paul
Hi Wes,
Further to this (and this is where I'm now getting very confused), I've updated the expression in the Multi-row tool to be:
IF Contains([Row+1:Name],"_Inc") AND [Row+1:Value]="Y" THEN
[Value]
ELSE
"0"
ENDIF
This updated expression correctly updates the benefit columns, but it also changes the values in all of the "_Inc" columns to 0 and I've no idea why!
These Y/N values are needed later in the downstream process so they need to be retained.
The final result of this code is:
Client | Ben1 | Ben1_Inc | Ben2 | Ben2_Inc | Ben3 | Ben3_Inc | Ben_ | Ben__Inc |
y | 0 | 0 | 0 | 0 | 1500 | 0 | 0 | 0 |
x | 1000 | 0 | 1100 | 0 | 0 | 0 | 0 | 0 |
Thanks,
Paul
Change the formula to this:
IF Contains([Row+1:Name],"Inc") AND [Row+1:Value]="N"
THEN 0
ELSE [Value]
ENDIF
You get this as your output:
David / Wes / Dan,
Thank you all for your help!