Refer to "generic" / "dynamic" multiple columns in a formula

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

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

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!

Datasets

Developer

Input

Accepted answers

davidskaife

Hi @Paul_s_Moody

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:

All comments

WesCannon

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

Dynamic Columns.yxmd

DanM

@Paul_s_Moody

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-Multi-Row-Formula/ta-p/86603

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

Paul_s_Moody

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

davidskaife

Hi @Paul_s_Moody

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:

Paul_s_Moody

David / Wes / Dan,

Thank you all for your help!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703