Hi all,

I'm trying to split multiple values from different columns into different rows following a logic and using TableMatch().

For example the table below is uploaded in Apptio:

On another table I want to use a TableMatch() that matches Linked Account ID and Application to give me Application Overwrite and Weighting. It will be done in two different TableMatch() formulas.

The issue is that after the TableMatch() is done and in case the Application Overwrite and Weighting is the one on the third row, I want to split that as the following:

Is there a way to do that that doesn't compromise performance and give the expected result? I thought about building the first table differently and doing a SplitEx() but that has a lot of room for errors and performance issue.

Thank you in advance.

Hi Caio,

I do not get why SplitEx would have performance issues or leave room for error, I have been using it quite often without any problems.

What you could do is a combination of SplitEx and Split, this would mean doing a SplitsEx on the Application Overwrite column and subsequent multiple column Split for the Weighting (in this case a maximum of 3).

Something like this:

The group step is just there to seperate the formula steps with the SplitEx in the first formula step.

In the second formula step, to determine the Index replace the SplitEx value with a "@", this simplifies the index determination.

Then using the Index and Split for the Weighting values within an If formula.

Performance seems really no issue, I tried with at least a 1000 lines.

Regards, Robert

Hi Ciao,

The performance issues related to the SplitEx are driven by the number of rows that are created in the table, at least that is my understanding. If the requirement is to multiple the number of rows in the table for each entry in the weighting field then however you do it the impact to performance is going to be the same. If anything the SplitEx might be the most efficient.

The other approach I was considering was to count the number of times that "," appears in the weighting field add one and then duplicate the rows and using a standard split formula with some character counting formulas and a row counter to pick the right part of the field. But when I was setting it up it seemed rather convoluted.