I am performing a lookup between my Server Master Data and my Applications which I want to see a list of all servers used for an application but I return a value of various but I would instead like an entire list.

Example:

Application Transform

Servers Master Data

I'm using a basic lookup here: =lookup(Application ID, Servers Master Data, Purpose, Server Name)

The return on this is:

but I would like to return this

Any suggestions on what function I should be using?

Thanks

Dru@@@

Hi,

If you use the LookupEx function instead of the Lookup function you should get the result you need.  So the formula would be:

=LookupEx(Application ID, Servers Master Data, Purpose, Server Name)

Be careful using this function, though, because it will significantly increase the number of rows in your data set.  That can have serious performance implications depending on the size of the initial data sets and your allocation strategies.  I would recommend speaking to your CSM before putting this into stage or prod.

Best,

Diane

Hi Diane,

Thanks for the quick reply. I had tried the LookupEx function but get the following error:

I have tried again to be sure I didn't miss anything but I have used the autocomplete for all fields and it looks to be correct when comparing to your sample and of course it does work using the simple lookup.

My actual formula:

=LookupEx(App Family, Servers Master Data, BU Consumer, Server ID)

Cheers 

Dru

Are you viewing the formula using the preview editor?  If you are, it'll throw an error by default I believe.  Hit save and see what happens bc your formula looks ok.

Hi Dru, Diane and Jenny,

This is a really useful discussion. Is there a quick way to see all Apptio functions, there use and parameters?

In a similar way to the way Microsoft document their Excel functions, for example https://support.office.com/en-gb/article/Lookup-and-reference-functions-reference

Many thanks in advance, 

Regards,

Colin

Hi Colin,

There is indeed! If you go on any of your Apptio instances, then click the help link on the very bottom, it should take you to a page that looks like the image I attached. Then just follow the links I clicked to get to the Formulas and functions homepage bit. From there, you can see all the types of formulas possible in Apptio, along with examples of how to use each one

Oli

Awesome. Many thanks Oli

You can also see the function list here on Connect  Functions: Annotated list 

Fantastic. Many thanks Jenny. I've bookmarked this in my TBM Bookmarks Folder  

And there is one more spot... quick reference.

Christopher Davidson put together this Function Quick reference that can be helpful to have handy when you  are writing formulas.

Cheers,

Deb

Thanks Deb. This will come in handy!

Thanks Jenny

As always you have come to the rescue. Yes I was using the preview screen and once the saved it the formula worked (partially). I am getting a result but it appears I only get the first server name in the list.

Should return about 40 servers for PWS and 80 for SAP but I only get one, I can't see any switches that change the result but will continue to look myself and will update the thread if I find the result.

Is this preview screen error a known bug? Would this also account for the issue I was having with a tablematch function last week and so many others I have abandoned over time?

Happy to help   Yeah, I've seen someone mention that lookupex/preview window issue before and have run into it myself.  It is a known issue per this thread.

Not sure, tho, if that affects tablematch, tho.  I'm not as familiar w/the tablematch function, but there's a ton of good stuff out there in this forum.  Very good thread on troubleshooting.

Very weird your LookupEx isn't returning all matches bc your formula looks correct.  

In looking at the Servers Master Data, you do have multiple server IDs per BU consumer?  

The only other thing I could think of is if you have some sort of grouping going on - do you have a 'group by' on App Family perhaps? 

Do you have more than one LookupEx in the transform?  

I can confirm the tablematch function returns the same error when using the preview screen. Now I have tested both I think I will change process and move to a tablematch as I am a bit concerned about LookupEx functions and the possible performance impacts.

I will continue to look at why I only have one return in my LookupEx as I am sure this will come up again at some point and I don't like to leave these unresolved.

In answer to your questions:

Yes i have multiple Server ID's per BU Consumer

There is no grouping applied to the transform

and no other LookupEx is used in this transform (This is a transform of a transform so it may be used in the underlying one, I will investigate)

Thanks again

Tablematch and LookupEx are different functions.  Tablematch will only return 1 record for each match and behaves as though it were a "If x=this than that, else...if this than that.....".  LookupEx will return a row for every match like "if this than that and that and that".  For both of these functions you may get an error on preview and have to save the override formula to view the resutls.

Was catching up on some community posts and ran across this one - I actually ran into this issue today for the first time - I'm used to errors appearing in the preview window for a LookupEx and then bam, it happened today with a TableMatch.  I guess this isn't fixed in later 11x versions (bc we just upgraded to 11.8.4, and then 11.8.5 a week later) - maybe 12

Here is an interesting article to check out regarding various. 

@Jenny Franklin 

Excellent strategy for pointing people to your ETBMA blog for likes, Vyacheslav Snitkovskiy!