xlookup

akprasa1

Hi All,

 

How do i do a xlookup in Anaplan?

 

I would like to have a Boolean identifier for the plant column based the locations in the rows.

Basically search the Loc 1 in the plant Column and give a true output against the plant for every occurrence.

Added module diagram for better understanding.

 

 

 

Thanks in Advance

ManjunathKN

Hi

Lookup in Anaplan uses List or Date as mapping.

if you want to lookup based on boolean then you need to create one more line item to bring the list and then Map.

Line item 4(to be added): IF identified? THEN ITEM(your list) ELSE BLANK
Loc 1 : Plant[ lookup : Line item 4]

Thanks,
Manjunath

akprasa1

Hi Manju,

Thanks for your response .

i am not trying to lookup based on a Boolean. The Boolean in the image is the expected result that i would like to have.

i am trying to search the Loc1 in the Plant Column and  expect a Boolean to be ticked against the Plant Column as identified.

 

 

 

 

ogilkons

Hi @akprasa1 ,

 

I am not sure I understand the purpose of Loc 1 column, so please correct me if I'm wrong.

I would suggest creating a new module with Plants as a dimension and a boolean line item named Identified (please see screenshot). Let's name this module "Plant Settings" 

 

So then you can use formula for Identified column:

Identified = Plant Settings. Identified [lookup: Plant]

 

Hope that makes sense.

 

Thanks,

Nikolai

 

KirillKuznetsov

@akprasa1 

Do i understand correctly that you want system to tick the boolean in cases when code in Plant LI is presented anywhere in Loc1 LI and does not matter which list element this Loc1 code belongs to?

If so - then you should use firstnonblank for that. The mid-step module with plant codes list as a dimension with a boolean "Any Loc1" LI and a formula ISNOTBLANK(<yourmodule>.Loc1[firstnonblank:<yourmodule>.Loc1]).

After this in Identified? LI should collect booleans using Any Loc1[LOOKUP:Plants]

 

akprasa1

Hi Kirill,

 

In the Image ,Plant column is a text formated ,the Loc 1 is also a text .

i want to search Loc1 -2350 in the Column Plant.If this search is successful,i should get a boolean ticked as shown in identified Column against every occurrence of 2350 in the Plant Column.

 

Hope this clarifies my question

KirillKuznetsov

@akprasa1 

Could you please describe the process a little bit more?

What are these codes? Do they frequently change?

May Loc1 have a unique code for each element in the list? Or it is some king of search mechanism?

 

akprasa1

These are Transaction data loaded. Plant column captures the Plant Names as text.

Loc 1 also captures a Plant Name.

Can we search the LOC1 Plant Name across the Plant Column and get the Boolean marked.

KirillKuznetsov

@akprasa1 

It cannot without a mid-step where you calculate which plant has been mentionned.

For this I would recommend you to add to your transactional process a list named "Plants for transactions" which will be uploaded with unique codes of plants.

Then this list can be used for analysis of the mentionings as I described earlier with FIRSTNONBLANK.