Lookup transformation in BODS: lookup_ext explained with examples
lookup transformation in bods
Also searched as
- bods lookup_ext example
- how to load data into bods
- lookup function in sap data services
Short answer
In SAP Data Services (BODS) there is no separate Lookup transformation box. Lookups are done with the lookup_ext function (or lookup and lookup_seq) inside a Query transformation, typically in a mapping expression or the function wizard. You give it a lookup table, the result column, a default and the compare condition.
Applies to: SAP BusinessObjects Data Services 4.x.
Adding a lookup to a data flow
- 1Open the data flow in Designer and add a Query transformation after your source.
- 2In the target schema, select the column to populate and open its Mapping tab.
- 3Click the function wizard, choose Lookup Functions, then lookup_ext.
- 4Select the lookup table, the column to return, the default value if not found, and the cache option.
- 5Define the condition, comparing the lookup table key to the input column.
- 6Validate the data flow, run it in a test job, and check unmatched rows go to a reject path or take the default.
lookup_ext in one view
The call has this shape: lookup_ext([table, cache_spec, return_policy], [result columns], [default values], [compare column, operator, expression]). The cache spec decides how the table is read, for example NO_CACHE, PRE_LOAD_CACHE or DEMAND_LOAD_CACHE. The return policy such as MAX or MIN decides which row wins when several match.
Use PRE_LOAD_CACHE for reasonably small reference tables used for many rows. Use NO_CACHE or demand load for very large tables or when the lookup data changes during the job.
lookup_ext([DS_REF.DBO.MATERIAL_MAP, 'PRE_LOAD_CACHE', 'MAX'], [NEW_MATNR], ['UNMAPPED'], [OLD_MATNR, '=', Query.MATNR])
Alternatives and when to use them
lookup is simpler and returns one value, lookup_seq picks by sequence column. For many columns or complex joins, a Query join in the data flow is often clearer and can be pushed down to the database. Use lookups for reference translation, joins for combining datasets.
Loading data into the target
To load data, define source and target datastores, build the data flow with Query transformations (including lookups and validation), and execute through a job. For SAP targets, load through the supported interfaces such as IDocs, BAPIs or the SAP-supplied load methods rather than inserting into tables.
Try it here, free
SAP S/4HANA Conversion and Migration Tools Reference
A lookup of the SAP tools used at each phase of an S/4HANA conversion or migration, what each does and when to use it.
Common pitfalls
- !Caching a huge lookup table in memory.
- !No sensible default, so unmatched rows silently load blanks.
- !Using a lookup where a join would push down and run faster.
- !Ignoring multiple matches and relying on arbitrary return order.
- !Loading directly into SAP tables.
How an ERP-grounded AI assistant handles this
In migration projects an assistant can profile source and mapping tables, list unmatched keys and draft the lookup rules for review. Netray runs this privately on-prem, so migration data stays within your network.
Frequently asked questions
Is there a Lookup transformation in BODS?
Not as a separate transform. The functionality is the lookup_ext function used in Query transformation mappings.
What is the difference between lookup and lookup_ext?
lookup_ext is more flexible: multiple return columns, multiple conditions, cache control and return policy. lookup is a simpler single-value version.
How do I load data into BODS?
Create datastores for source and target, build a data flow with a Query transform, put it in a job and execute it from Designer or the Management Console.
Why is my lookup slow?
Likely no cache or a very large table. Preload where the table fits in memory, or switch to a join pushed down to the database.
Related
SAP MM Error Messages List: Reading Codes and Changing Message Severity
SAP messages are identified by class and number, for example M7 021 or ME 172. No official complete list fits every release, so the reliable method is SE91: enter the class and number to get the exact text, long text and where used. The class prefix tells you the area (M7 inventory management, M8 invoice verification, ME and MEPO purchasing), and many messages can be downgraded from error to warning in customizing.
Error fixSAP QM: Inspection Lot Not Created at Goods Receipt
An inspection lot is created only when QM is active for the plant, the material has an active inspection type for the goods movement, and the material carries a QM control key. Check the Quality Management view in MM02, confirm inspection type 01 (purchase order) or 04 (production order) is active, then check QM activation in the plant and the goods movement used.
Error fixSAP M7 021: Deficit of SL Stock Quantity
M7 021 fires during a goods issue, delivery, or confirmation when SAP checks the unrestricted-use (SL) stock for the exact plant, storage location and batch combination and finds less than what you are trying to post out. Fix the entry (batch, storage location, quantity) first; only enable negative stock or downgrade the message to a warning if the business genuinely needs to post ahead of a receipt.
Error fixSAP IDoc Status 51: Application Document Not Posted
Status 51 tells you the IDoc reached the application layer and failed to post, but the status text itself is not the error - the real cause sits in the status record's long text or the linked application log. Fix the underlying data or configuration, then reprocess the IDoc through BD87 rather than editing the status.
Error fixSAP Short Dump TIME_OUT: Maximum Runtime Exceeded
A TIME_OUT runtime error means a dialog (or RFC) work process ran longer than the maximum allowed runtime, controlled by profile parameter rdisp/max_wprun_time (600 seconds by default). The fix is almost always to move the long-running work to background processing or tune the underlying program, not to raise the timeout.
How-toHow to Read MD04 Exception Messages in SAP
The exception indicator in MD04 flags procurement elements whose date or quantity no longer matches the current demand situation - it is SAP's built-in change signal, read element by element in the detail screen, not just the header icon. Triage by exception group first, then decide to reschedule, cancel, or accept each one.
AI for ERPAI-assisted data migration for ERP implementations
AI speeds ERP data migration by profiling legacy data, proposing field mappings, and flagging cleansing issues before cutover, with a human validating every rule.
AI for ERPAI for SAP S/4HANA, Running On-Prem or in Your Private Cloud
Run AI on SAP S/4HANA without sending ERP data to a public API. On-prem and private-cloud architecture, CDS views, OData, and honest deployment trade-offs.
Stuck on SAP BusinessObjects Data Services?
Talk to engineers who work inside SAP BusinessObjects Data Services every week, and who build private AI that answers these questions from your own ERP data.
Keep exploring
Free tools
- SAP S/4HANA Conversion and Migration Tools ReferenceA lookup of the SAP tools used at each phase of an S/4HANA conversion or migration, what each does and when to use it.
- SAP MM Fit-Gap Analysis Template with ExamplesAn editable SAP MM fit-gap worksheet with realistic example rows, resolution options and effort totals.
- SAP Data Migration Object WorksheetAn editable register of S/4HANA migration objects with record counts, cleansing progress, load method and mock load effort.
ERP fixes and how-tos
- How to Map and Transform a BOD in Infor ION ConnectHow to build a mapping in ION Desk between two BOD or message types, handle custom fields and lookups, test with the Monitor and fix common mapping errors.
- Infor ION Scripting and ETL: Transformation Options ExplainedInfor ION scripting and ETL explained: mapping and transformation in document flows, where scripting fits, and when to use Data Lake flows or another ETL tool.
- SAP PP and MM Enhancement Examples: User Exits, BAdIs and Enhancement SpotsPractical SAP PP and MM enhancement examples: user exits, BAdIs such as ME_PROCESS_PO_CUST and WORKORDER_UPDATE, how to find them and a PO validation sketch.
Guides
- ERP Data Mapping Best PracticesMaster ERP data mapping with source-to-target techniques, transformation rules, and field-level documentation. Prevent data loss during ERP migration.
- Baan to SAP Migration vs Infor LN: Baan ERP vs SAP S/4HANABaan to SAP migration versus Infor LN: compare functional fit, the 12-18 vs 18-30 month timelines, customization rework and cost for Baan ERP manufacturers.
- ERP Data Migration Strategy GuidePlan a successful ERP data migration with a phased strategy covering extraction, transformation, loading, and validation. Reduce migration risk by 80%.
AI on your ERP
- AI Agents Over Infor ION API, BODs, and the Infor Data LakeBuild AI agents on Infor ION API Gateway, BODs, and Data Lake, alongside or instead of Infor's own GenAI features, with your choice of model and hosting.
- AI-assisted data migration for ERP implementationsAI speeds ERP data migration by profiling legacy data, proposing field mappings, and flagging cleansing issues before cutover, with a human validating every rule.
Integrations
- How to add Slack to SAP ECC 6.0Build a Slack app for SAP ECC 6.0: slash commands that read stock and orders via BAPIs, interactive approvals, alert channels and Socket Mode for on-prem.
- How to add AI agents to SAP Business ByDesignHow to add AI agents to SAP Business ByDesign: a tool layer over its web services and custom OData, with human approval before any write. Steps, flows, pitfalls.