Data Mapping: Add Formula getting very slow with many lines

daniel · July 22, 2014 11:26AM

I'm using a very long list of IF functions for a particular report to group up some placement names into renamed categories, however beyond a certain point of number of lines, it will get quite slow and sometimes freezes the screen processing this.

Could this be looked at to see if whether there's a way to optimize it? I'm finding I am adding more and more lines to the IF function and it will get more unstable.

I think it's slow because it tries to apply the function as I try to rewrite it and keeps processing them in the background, perhaps there's a way to stop the processing part until I finish writing in the full set of IF functions?

paola · July 22, 2014 12:22PM

The above IF formula contains about 100 statements, with different outcomes, so it is a case of 'data mapping', therefore it might be more practical to use the Merge operation, in order to merge the original dataset with the list of current field values, next to the field with desired values, creating a link on the [Current value], while keeping after merge only the [New value] column.
Merge operation block also supports Contains and multiple field merge criteria, so you can set conditions across multiple fields, making sure that, for example, value "A" is replaced with "B" in the field [Name] only if [Value] =5...

steve · July 23, 2014 1:09AM

Daniel -
Try unticking "Settings > Advanced > Performance > Partial result calculation in formula editor". Or avoid moving your mouse over the formula in the formula editor dialog. This should help if you do keep your current approach. It disables a usually very useful feature of inspecting partial results for the part of the formula under the mouse in hover tooltips, but for very large formulae it can perform poorly.

	Slow IF functions.iok	24K
	TestDataforQuery.xlsx	31K

Welcome!

Categories

Ideas Parade

Tagged