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

Developer

Parse

Common Use Cases

Batch Macro

Accepted answers

allwynthomas24

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

Problem 1_nikitapuniani.yxzp

nikitapuniani

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.

All comments

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

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

Problem 1_nikitapuniani.yxzp

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.

0208-nikitapuniani.yxzp

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

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703