0Lookup 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?
-
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
-
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'
)
)
-
Mukul Ranjan what is the key of table SHIPMENT_REFNUM in your schema definition?
-
-
its combination of shipment_gid and shipment_refnum_qual_gid
-
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?
-
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' -
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.
-
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.
-
-
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
-
Dan Brock Thanks Dan
-
-
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
