Is Lookup within same module possible?

sharmagh

Hi,

 

Is it possible to apply lookup within same module? I know direct link can work but, i need to get value of a particular list item of the same module.

 

Thanks!

 

Misbah

Can you please share screenshot of what are you trying to achieve? I think you are talking about  creating Lookup modules which we create in order to avoid Select statement on list items. 

nathan_rudman

Yes you can use a LOOKUP within the same module if you have a subsidiary view (although not very common)

One thing to note is that for the engine, modules don't exists. That you have a line item in a module or in another, it doesn't matter for the engine, performance for getting it is the same. There is no need to bring a prop into a module to be used by this module, you can reached for it anywhere in the model.

sharmagh

Sorry Misbah, as I am working on my Organization's data I can't share any screen shot however, I can try to elaborate a bit more:

My module has 2 lists with their Top Levels (All Funds & All Deals),

Both the lists are being used in the rows and 3 line items in the Columns (Opn. Bal, Amount Utilized and Clos. Bal).

Each deal will show all the funds under it and calculate all the line items, the only problem is I am not able to carry forward my Clos bal as Opn Bal for the next Deal, so I thought if their is any way to do the same with the help of lookup.

I have created codes for each fund and Deal a like D1, D2... and F1, F2...(both are formatted as list) and added a line item in my current module to get the same, now the formula is something like this:

IF Deal Code = D1 then get the Opn Bal for all the funds from another Module ELSE Previous set of Fund's Closing Bal.

sharmagh

@Misbah 

Sorry Misbah, as I am working on my Organization's data I can't share any screen shot however, I can try to elaborate a bit more:

My module has 2 lists with their Top Levels (All Funds & All Deals),

Both the lists are being used in the rows and 3 line items in the Columns (Opn. Bal, Amount Utilized and Clos. Bal).

Each deal will show all the funds under it and calculate all the line items, the only problem is I am not able to carry forward my Clos bal as Opn Bal for the next Deal, so I thought if their is any way to do the same with the help of lookup.

I have created codes for each fund and Deal a like D1, D2... and F1, F2...(both are formatted as list) and added a line item in my current module to get the same, now the formula is something like this:

IF Deal Code = D1 then get the Opn Bal for all the funds from another Module ELSE Previous set of Fund's Closing Bal.

Misbah

@sharmagh 

 

See if this helps. This approach is to just give you an idea how this can be implemented. Here you might get Circular References because Closing balance = Open Balance - Amount Utilized, unless and until you use PREVIOUS function which is not feasible in Non time dimension modules you will be welcomed by circular reference error. That's why you see I have removed the formula in Closing Balance line item just to show it to you how LOOKUPs in list items can be used.

 

Step 1: Create a mapping module between the deals. Here I have created a property mapping, you stick to the module

Step 2: Write the formula using Lookup mentioned in the snip.

 

sharmagh

@Misbah 

 

Getting the below Error

Format of mapping used for lookup doesn't match any dimension of the source

Misbah

@sharmagh 

 

Make sure source is List formatted line item. Check out my screenshots in previous reply

sharmagh

@Misbah i am also using the list formatted line item

Misbah

@sharmagh 

 

In that case it would be great if you can post screenshot of your source line items, target line item along with the dimensionality. You don't need to show the data so that we can help better

sharmagh

@Misbah  could you please tell me more about the steps you took to create Properties Mapping and Mapping Module, may be i am doing something there.

sharmagh

@Misbah 

please see below

Target Module View 1 :

 

Target Module View 2:

Opening Bal:

Deals List Properties:

Deals and Fund List Grid View:

 

sharmagh

Ok, That means if i am not using time scale then this lookup can't work due to circular reference?

Misbah

@sharmagh 

 

This formula should work

 

IF ITEM(D Deals) = D Deals.'D1' THEN Opening Balance.Op Bal ELSE Closing Balance[LOOKUP: D Deals.Prior Deals] 

 

The above formula will work only if your Closing balance is not derived from Opening Balance. There will be a circular reference

sharmagh

@Misbah 

 

Then how my Cl bal will get updated?

Misbah

@sharmagh 

 

That's exactly what I have been trying to communicate. You can't use this if your Closing Balance is Derived from Opening Balance due to circular references.

sharmagh

Thank you!

saurabh.raheja

You could try creating another line item, say 'Opening Balance (formula)' where you use the lookup formula on Closing Balance. 

Then create an action to import from 'Opening Balance (formula)' to 'Opening Balance' line item. 

This approach isn't super intuitive and user friendly, so the question to be asked before applying this is how frequently does this calculation update. If it updates very frequently, then this might not really be useful, since every time the calculation changes, the Import action needs to be run (either manually or using API) to update Opening Balance. But in case this doesn't change very frequently, then this might work.

 

Regards

Saurabh

sharmagh

It changes frequently