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

[Paul_s_Moody]

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!

[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

[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...

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

[Paul_s_Moody]

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

[DavidSkaife · Accepted solution]

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!