Apptio

Hi All,

This is a formula to identify something based on number range.

=if(x>0 AND x<=5, "with in 5", if(x>5 AND x<=15, "Between 6 to 15", if(x>15 AND x<=20,"between 16 to 20","Greater than 20")))

Can this be done using table approach in Apptio? instead of hardcoding the numbers in condition, Is there any way to keep it in table(like below) and find it using Apptio functions?

Please advise. Thanks in advance

Hello @Marimuthu Senthilkumaran

You can do it with a TableMatch() function call. 

I had to tinker with the data a bit, as I couldn't get it to work as described in the help pages (link):

I tried as in the example within brackets, but it didn't work. Then I remembered that TableMatch scans the rows sequentially, until it finds a match. So I prepared the following table. I called it Test Tablematch:

And created a test table with the following content:

In the table, I added a simple formula step like so:

Result = TableMatch(Test Tablematch,Result)

This is what Apptio returned:

0    with in 5
1    with in 5
2    with in 5
3    with in 5
4    with in 5
5    with in 5
6    Between 6 to 15
7    Between 6 to 15
8    Between 6 to 15
9    Between 6 to 15
10    Between 6 to 15
11    Between 6 to 15
12    Between 6 to 15
13    Between 6 to 15
14    Between 6 to 15
15    Between 6 to 15
16    between 16 to 20
17    between 16 to 20
18    between 16 to 20
19    between 16 to 20
20    between 16 to 20
21    Greater than 20

Which, I believe, is what you were after. You may want to put some more rules to handle with other cases, such as there being a negative number, but you get the gist.

The TableMatch() function is a strange animal, in that you only pass the table name and the column you want the function to give you back. Also, you must ensure column name(s) match, i.e. the column holding the data in your table must match the one with the rules in the ancillary table.

You don't have to call Result the column that will hold the result of the function call, but I think it is clearer like this.

Hi All,

This is a formula to identify something based on number range.

=if(x>0 AND x<=5, "with in 5", if(x>5 AND x<=15, "Between 6 to 15", if(x>15 AND x<=20,"between 16 to 20","Greater than 20")))

Can this be done using table approach in Apptio? instead of hardcoding the numbers in condition, Is there any way to keep it in table(like below) and find it using Apptio functions?

Please advise. Thanks in advance

Hi Guillermo, Thanks for explaining this in much detail.  This is really helpful and I followed your second option. Thanks again

I also tried the first option with the brackets, but it didn't worked. Curious to understand why it is not working? 

"TableMatch scans the rows sequentially" - Still not able to follow why it didn't worked.

Thanks again.

Hello @Marimuthu Senthilkumaran

You can do it with a TableMatch() function call. 

I had to tinker with the data a bit, as I couldn't get it to work as described in the help pages (link):

I tried as in the example within brackets, but it didn't work. Then I remembered that TableMatch scans the rows sequentially, until it finds a match. So I prepared the following table. I called it Test Tablematch:

And created a test table with the following content:

In the table, I added a simple formula step like so:

Result = TableMatch(Test Tablematch,Result)

This is what Apptio returned:

0    with in 5
1    with in 5
2    with in 5
3    with in 5
4    with in 5
5    with in 5
6    Between 6 to 15
7    Between 6 to 15
8    Between 6 to 15
9    Between 6 to 15
10    Between 6 to 15
11    Between 6 to 15
12    Between 6 to 15
13    Between 6 to 15
14    Between 6 to 15
15    Between 6 to 15
16    between 16 to 20
17    between 16 to 20
18    between 16 to 20
19    between 16 to 20
20    between 16 to 20
21    Greater than 20

Which, I believe, is what you were after. You may want to put some more rules to handle with other cases, such as there being a negative number, but you get the gist.

The TableMatch() function is a strange animal, in that you only pass the table name and the column you want the function to give you back. Also, you must ensure column name(s) match, i.e. the column holding the data in your table must match the one with the rules in the ancillary table.

You don't have to call Result the column that will hold the result of the function call, but I think it is clearer like this.

Hi All,

This is a formula to identify something based on number range.

=if(x>0 AND x<=5, "with in 5", if(x>5 AND x<=15, "Between 6 to 15", if(x>15 AND x<=20,"between 16 to 20","Greater than 20")))

Can this be done using table approach in Apptio? instead of hardcoding the numbers in condition, Is there any way to keep it in table(like below) and find it using Apptio functions?

Please advise. Thanks in advance