Default Excel Formula in the Output

[BobjiK]

Hi

This is the output file. In this, Billing FTM = Val.in.rep.cur + Adjustment + True Up.

The Adjustment & True Up columns are subjected to user interpretation. If the user changes the Adjustment or True Up, it must be reflected under Billing FTM. 

For eg., for the 1st row, if Adjustment is changed to 100 and True Up by another 100, Billing FTM must reflect 5200.34

The problem is that once the Output file is created, Alteryx's job is finished. Even if the user modifies the value in those 2 columns, it will not be reflected under Billing FTM.

My idea is to somehow copy the formula, Val.in.rep.cur + Adjustment + True Up behind every cell. Just like how Excel works. 

Is this possible? Or if there is another method, please let me know

Row	G	H	I	M
1	Val.in rep.cur.	Adjustment	True Up	Billing FTM
2	5000.34	0	0
3	5000.23	0	0
4	1234.55	0	0
5	1266.22	0	0
6	14047.58	0	0
7	11459.48	0	0
8	-6402.01	0	0
9	-2411.94	0	0

[Carlithian]

If you are outputting to excel you can just write the formula into the field. You need to work out what your column letters and row numbers are and add them in.

So in the formula tool you have something like

Billing FTM 

Val.in.rep.cur + Adjustment + True Up

but you want something like 
Billing FTM

"=SUM(A" + [RowID] + ":C" + [RowID] + ")"

[BobjiK]

Thanks @Carlithian 

Would you mind sending a sample WF?

I am new to Alteryx.

[BobjiK]

Hi @Carlithian 

I have updated the table with rows and columns. Would you please explain based on that?

[Carlithian]

So here is an example, but there are some important things to consider 

RecordID - this is created in the workflow, I am assuming that your file will have data from row 2 down, with row 2 containing the headers.

The columns for the formula are hardcoded in this example, to be G H and I, as such I have added in filler columns so that the data sits in the correct column for the output. If this needs to be dynamic you will need to do work to identify what the excel column letter would be within the workflow itself. 

Hope this helps

[BobjiK]

Thanks @Carlithian 

Let me try this.

[BobjiK]

Brilliant @Carlithian 

The solution works perfectly. One question though. For now, I will tell them that the columns need to be static but what if the column is dynamic? How can I handle such a situation?

[Carlithian]

Okay, imagine that instead of G H and I  the columns were now in D H and K. The name of the field should still be the same 

Transpose the data in alteryx so you have something and then give it a row ID
Name, Value
field 1, 1

field 2, 2

field 3, 3

Val.in rep.cur., 4

You can convert this value to a letter by converting it to a char 

CharFromInt([Value] + 64) 
 Because the unicode value for A is 64, adding 64 to the value will mean that the first row will give you back A, which is its positional equivalent in the excel file. 

Name, Value

field 1, A

field 2, B

field 3, C

Val.in rep.cur., D

I would then filter out the rows that relate to the columns you care about,  update the Name to include _columnReference or something after it. Transpose the data back, then use that field in the creation of the excel formula. 

"=Sum(" + [Val.in.rep.cur_columnReference] + " + ToString([RecordID]) + "," + [Adjustment_columnReference] +"" + ToString([RecordID]) + ", + [True Up_columnreference] + " + ToString([RecordID]) + ")"