Problem with replacing SELECT with LOOKUP

TahaU

Hi everyone,
I am replacing SELECT functions with LOOKUP as a part of model optimization (2.02-14 Avoid using SELECT). I did it lots of times and they worked but I am struggling with couple of them. 

My approach is that I use mapping module to use LOOKUP instead SELECT. I add list formatted line item and select the list item (which is used by SELECT function) from dropdown. Then I use LOOKUP with this line item.

I added screenshots of the formulas for 2 line items. They should have same results but they are not. Can you help me to understand what is wrong here?

Thanks

M.Kierepka

Hi @TahaU,

It depends if the list you are using in your SELECT/LOOKUP is just a flat list, or it's hierarchy. If it's ragged hierarchy (multiple levels in one list), then you can "SELECT" upper levels, but you can't "LOOKUP" them (lookup column worked for leaf item B12, but failed for A1, A2 and Total), below a simple demo:

 

 

M.Kierepka

Hi @TahaU,

You can use ISANCESTOR() formula, here you can find How-To article how it may be used on some occasions, and below is my example PoC for your problem:

 

JaredDolich

@TahaU 

A few observations.

• When you use LOOKUP (or SUM) you have to refer to a list formatted line item. You cannot reference a numeric or text. In your lookup it appears as though you are using a numeric (REV_COGS_VAR_COST)
• Regarding optimization, I believe the planual is trying to have you avoid "hard-coding". This happens frequently when using the SELECT function. To be honest, I only use SELECT on top level values since they rarely change.

TahaU

Hi @JaredDolich,

 

Thank you for replying.

It is list formatted line item that I referenced. Can you check the attached screenshots?

 

 

 

JaredDolich

@TahaU 

Excellent. That line item needs to be in your TARGET module not SOURCE module. When you use SUM, you'll have that in the SOURCE. For LOOKUP, you need to have the line item in the TARGET, or preferably in a system module that has one of the lists from your TARGET.

You're almost there.

M.Kierepka

Hi @TahaU,

Your approach is generally correct, but more context is needed to determine what goes wrong. Just looking at the formula, it can be anything. Maybe you are referencing upper level with SELECT? And maybe the source has "Formula" summary that affects the result in some way? Perhaps the two line items are not identical, like they have different settings/dimensions?

Misbah

@TahaU 

 

It's important to understand the dimensionality of Source and Target modules even when you try to replace SELECT with LOOKUP. You might be thinking that this guy is crazy, what is he even talking about. 

 

What if I tell you that LOOKUP from dimensionless module can't replace SELECT in one scenario or in other words it can't give you the same output and that scenario is:

"When you have one subset of a list in Source Module and another subset of the same list in the target module". In this case you will have to use that list as dimension in the Global module and then do LOOKUP.

Hope that helps

Misbah

Miz Logix

TahaU

Hi @Misbah,

Thank you for replying. I wasn't aware of something like that and it is great to know.
I checked dimensions of source and target modules and they don't have subsets of same list.
However I like to mention that source module has the subset of the reference list (Rev_COGS Account). Can it be a problem?

TahaU

Hi @JaredDolich,

 

My target module is IC_05 and my source module is IC_04. The line item that I reference is in a global module. It should work I think.

SELECT formula: 'IC04_Resale Stage'.Lookback Rate from InterCo[SELECT: Rev_COGS Account.Variable Cost]
LOOKUP formula: 'IC04_Resale Stage'.Lookback Rate from InterCo[LOOKUP: 'SYS01_ALM Lookups'.'Rev_COGS-Variable Cost']
SYS01_ALM Lookups is global module which includes list formatted line item. (Rev_COGS Account)

TahaU

Hi @M.Kierepka, 

 

Thank you for your answer. I checked the things that you said. 
- The 2 line items are identical
- Source doesn't have formula summary

I don't know the "referencing upper level with SELECT"? Could you explain what does it mean? Also what else should I check?

TahaU

Hi @M.Kierepka,

 

Thank you for the example.

It is hierarchical list. And I was trying to use LOOKUP for Variable Cost. I can see that why it is not working.

I tested what you said, the LOOKUP worked for the leaf items while it was not working for upper levels.

So, what should I do for upper levels to avoid SELECT?