need to calculate formula in big size(100 mb) excel through Altreyx tool

[nadanl]

I already done the formula's (refered within the same excel) and calculate option is in manual and now need to calculate formula's in big size(100 mb) excel through Altreyx tool, Because in excel taking too much time. if anybody knows the solution kindly help me on this...

[GarthM]

hi @nadanl

would it be possible to get more information, or an illustration of the expression you're using in excel?

[nadanl]

Hi

thanks for ur reply, i just used multiple vlookup formula's (to get data's from multiple sheet) hence its taking more time to calculate those formula's in excel.
my expectation is (i will do the wrkings in excel itself) just to recalculate those formula's through this tool. can u pls help me on this? pls...

[KaneG]

Hi @nadanl,

Was there meant to be a sample file attached? Have you got an example of the formulas? I'm guessing that the excel file that you are referencing is 100MB and so you can't attach it. 

VLookups are normally replaced with the Join tool in Alteryx, however it can be a combination of tools, depending how advanced the formula is. Try bringing in a couple of your sheets using Input tools, then using the Select tool to deselect the fields you don't wan't before using the join tool to join them together.

Kane

[nadanl]

Hi

Thanks for the reply,

I did formula's in excel and i changed the Calculation option as "Manual"(In excel:Formula-->calc option-->manual),if i change it as "Automatic" the calculation will be done and i will get the output file. but its taking more time. as of now i dont want to fine tune the formula's.

My only expectation is using this tool "need to change the "Manual into Automatic"(In excel:Formula-->calc option-->manual as Automatic) then only the formula wil calculate and i can get the output with short span of time.

can u pls help me on this.

[KaneG]

Hi @nadanl,

I still don't think that I understand. Are you saying that you want Alteryx to change the option in Excel? Or would ytou like some of those formulas to be processed in Alteryx so that the output from Alteryx already has the data calculated, hence you won't need the formulas at all?

Can you provide a sample of the first 100 lines or something similar?

[nadanl]

Yes , I want the alteryx tool need to change the excel formula option(to calculate those formulas) and calculate the formuas and want output,

I herewith attached the excel file.

in the attached file Sheet name"Cross check" is taking more time to calculate the formula in excel, (each sheets should have 50,000 lines) so i need a help to resolve my issue.

[KaneG]

Hi @nadanl,

In your example, you would just have to bring in your spreadsheet for each sheet. 

And then join them together on your Plant Mat, and use a formula tool to do your calculations.

Kane

[KaneG · Accepted solution]

Hi @nadanl,

I had another look at your issue while in training today and once I figured out what the layout of the data was and what you're trying to acheive, it kind of all just fell out...

You would need to clean up some of the fields as there is a lot of redundancy, but the key is to transpose all your cost fields so that you have less calculations. I've attached the module below with comments so that you can follow it through.

Kane

[nadanl]

Thank you very much for ur suggesstions, I will check from my end.