xlookup

akprasa1
akprasa1
in Modeling

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

Tagged:

Answers

  • ManjunathKN
    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
    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
    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" 

    ogilkons_1-1643709938651.png

     

    So then you can use formula for Identified column:

    Identified = Plant Settings. Identified [lookup: Plant]

     

    Hope that makes sense.

     

    Thanks,

    Nikolai

     

  • KirillKuznetsov
    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
    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
    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
    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
    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.

Categories