0

Lookup function

I am trying to use lookup function for a scenario having values in a list e.g.

lookup(result lookup field, primary key field 1, primary key value 1, primary key field 2, (primary key value 2 or primary key value 2),...,default value)

is it possible to achieve this without having multiple lookup functions?

11replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Mukul, this is how the lookup function works, but it usually works on a table, where you defined your (pkey_field1, pkey_field2,...) as a table key, using the Function field in the table screen.    In your message, you are saying ' a scenario having values in a list'.  Can you please explain this scenario more.

    Regards,

    Hichem

    Reply Like
  • Thanks Hichem for replying, I am trying to achieve below

    Here I am pulling shipment_refnum_value from shipment_refnum table where shipment_refnum.shipment_gid is matching with shipment.shipment_gid and shipment_refnum.shipment_refnum_qual_gid in ('SBUX.CMS','SBUX.SOURCE_SYSTEM').

    I tried using below formula column, its not throwing any validation error, but not sure if this is right.

    Also I tried with two lookup functions with ifnull() but I am trying to avoid multiple lookup functions.

     

    lookup(

            SHIPMENT_REFNUM.SHIPMENT_REFNUM_VALUE,

            SHIPMENT_REFNUM.SHIPMENT_GID,

            SHIPMENT.SHIPMENT_GID,

            SHIPMENT_REFNUM.SHIPMENT_REFNUM_QUAL_GID,

            in(

                'SBUX.CMS',

                'SBUX.SOURCE_SYSTEM'

            )

        )

    Reply Like
    • Mukul Ranjan what is the key of table SHIPMENT_REFNUM in your schema definition?

      Reply Like
  • its combination of shipment_gid and shipment_refnum_qual_gid

    Reply Like
    • Mukul Ranjan So for one value of SHIPMENT_GID (like Shipment_gid=10),  I can have two values of SHIPMENT_REF_NUM_VALUE: one for SHIPMENT_REFNUM_QUAL_GID='SBUX.CMS', and one for SHIPMENT_REFNUM_QUAL_GID='SBUX.SOURCE_SYSTEM'.  Correct?

      If this is the case, how can we store these two values in one column?

      Reply Like
    • Hichem Sellami yes, the table it has more columns, but this combination is enough to identify one unique row

      Table: SHIPMENT_REFNUM

      shipment_gid      shipment_refnum_qual_gid      shipment_refnum_value

      10                              SBUX.SOURCE_SYSTEM            System 1

      11                              SBUX.CMS                                           CMS Provider 1

      Ideally in source system (OTM) above 3 columns are keys (as in sample above) but in reporting we need to pull shipment_refnum_value for a combination of shipment_gid and shipmet_refnum_qual_gid, so in Incorta we have made first two columns as key and pull the third column. 

      SQL equivalent I am trying to pull is as below, I trying to have this built as formula column in Shipment table

      select shp.shipment_gid, 
             coalesce(shp.attribute12, sr1.shipment_refnum_value, sr2.shipment_refnum_value)
        from shipment shp
             left outer join shipment_refnum sr1 on shp.shipment_gid = sr1.shipment_gid and sr1.shipment_refnum_qual_gid = 'SBUX.CMS'
             left outer join shipment_refnum sr2 on shp.shipment_gid = sr2.shipment_gid and sr2.shipment_refnum_qual_gid = 'SBUX.SOURCE_SYSTEM'

      Reply Like
    • Mukul Ranjan in your example, SHIPMENT_GID is a key, because each row has a different value.  Can we have the same example, with both records having shipment_gid=10?    If not, we can remove the additional condition on shipment_refnum_qual_gid from the lookup.

      If yes, even for two records out of 100M, we need to define this as two separate columns.

      Reply Like
    • Hichem Sellami Thanks Hichem for looking into this. I am comfortable in using lookup function, but looks like this scenario lookup is not going to work and I am working on different solution for this requirement.

      Reply Like
  • For anyone else, this post provides a bit more information about how to use the function:  https://community.incorta.com/t/63jl20/how-to-use-the-lookup-function

    Reply Like
  • Mukul Ranjan  please refer to this video to see how to model the SQL that you shared in this post in Incorta.

    Video:

    - https://youtu.be/ahk0z468u7Y

     

    I have created sample data for 2 tables that you have used in this SQL.

    This video shows how you can model this SQL in Incorta using

    - Joins

    - Alias table

    - Formula

    Reply Like
Like Follow
  • 7 days agoLast active
  • 11Replies
  • 27Views
  • 4 Following