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

xlookup_131203.PNG

Accepted answers

All comments

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.

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…