Excel to drive calculations in Alteryx

[nikitapuniani]

Hi,

I have an excel mapping file that has calculations

Example

ID Product Account   Math key

1.      ABC.    SDF345   a

2.      ABC.    SDF345   b

I want to create below

3. ABC SDF345  c=a-b

I have a long list of such mappings/calculations that I want to drive off from excel. Is this even possible? Because each and every row has a different "Math Key" its getting painful to do each line and then union the results. My workflow looks like giant mess. Any easier way to get around this? 

Thanks

[Qiu]

@nikitapuniani 
I did one before is that output the variable needed to a predesigned Excel, with your own formula, then read the Excel calculated result back.
can you provide some sample ?

[nikitapuniani]

Hi Qiu,

Thanks for the response. Yes correct. Just to clarify...

I have an excel file below like this 

ID Product Account   Math key Amount 

1.      ABC    SDF345   a.               5

2.      ABC    SDF345   b                3

3.      ABC    SDF345  c=a-b.        2

4.      XYZ     WDF942  d                130

5.      XYZ     WDF942  e                 10

6.      XYZ     WDF942  f=d+e        140

The non bold text are my inputs. And I want to calculate the lines in bold to get the values in the "Amount" column. I want to drive the Alteryx calculations off this excel spreadsheet using the "Math Key" column

It would be great if you could send me the solution if you have done this before. 

Reason for doing this : My excel file has many such calculations and it is getting increasingly difficult to manage these calculations within alteryx itself. Hope this helps! Let me know if you have any questions

[allwynthomas24 · Accepted solution]

Hey @nikitapuniani,

Here is my Solution to your requirement.

Please Check the Output of the Workflow and confirm whether its giving the output as per your expectations.

I have attached Workflow package (.yxzp file) which contains the Alteryx Workflow, above mentioned demo data in Sheet1 and the Output of the workflow is saved in the same Excel Workbook in Sheet2.

Thanks & Regards,

Allwyn Thomas

(Workflow Image)

Spoiler

[Qiu]

@nikitapuniani 
Thank you for the additional information.
Here is a quick sample and hope you can modify and use it in your real flow.

[Kamran1991]

Hi @nikitapuniani,

This can be solved by using 'multi-row formula'. Please share a dummy database, then will be able to write an exactly solution?

Kamran

[nikitapuniani · Accepted solution]

Hi @allwynthomas24

Yes! This is exactly what I was looking for. I had no idea we can use formula within a select tool.

Thank you so much!

I still have more calculations down the line where I will using these derived rows - Math Key (c,l,i,f) subsequently for further calculations. eg. m= c+l-i-d OR n=c+i+l+f. I will need to think more on that. I prob will narrow down those causes by applying suitable filters and then use multi-row formula tool without the operator. 

I will get back to you if needed once I make some progress but I am hoping this should solve it. 

[nikitapuniani]

Hi Qiu,

This will not work for me. Because I want to be able to drive these calculations dynamically by using the "Math key" for difference operators and different inputs

Thanks for looking into it. Appreciate your time and effort :)

[allwynthomas24]

Hey @nikitapuniani,

I am glad that the solution worked out for your current requirement.

Thanks for flagging it as the solution.

The community would be more than happy to help you regarding alteryx queries.

Thanks & Regards,

Allwyn Thomas