topic Re: Join SAP HANA tables using source_lookup in Qlik Replicate

Join SAP HANA tables using source_lookup

Al_gar — Sun, 28 Jan 2024 22:59:40 GMT

I'm trying to use the Replicate source_lookup function to join SAP HANA tables, but the task just fails.

Source - SAP HANA with Trigger-based CDC
Target - MS SQL Server
Replicate - Nov 2022

Here's an example:

Source tables: sch.Product, sch.Category
Target table: dbo.Inventory

In the Transform tab of the table named sch.Product I added column named Sales_Category with expression: source_lookup('NO_CHACHING','sch','Category','category_name','cat_id=?',$cat_fkid).

However, when I start/reload the task it will fail and trace log for SOURCE_CAPTURE shows "Data lookup statement for table 'sch.Category' was not found in the pool, going to allocate a new statement (statement_manager.c:2132)". However that table does exist in the SAP HANA database.

How can I fix this error and get the source_lookup to work?

At the moment I'm trying to join 2 tables, but is it possible to join more than 2?

Re: Join SAP HANA tables using source_lookup

john_wang — Mon, 29 Jan 2024 02:34:28 GMT

Hello @Al_gar ,

Thanks for reaching out to Qlik Community!

However, when I start/reload the task it will fail and trace log for SOURCE_CAPTURE shows "Data lookup statement for table 'sch.Category' was not found in the pool, going to allocate a new statement (statement_manager.c:2132)". However that table does exist in the SAP HANA database.

This messages means the SQL Statement does not exist, Replicate will build it; not means the table does not exist.

At the moment I'm trying to join 2 tables, but is it possible to join more than 2?

I do not see it's doable within Qlik Replicate source_lookup, the design is complex and the performance is not good. I'd like to suggest you define VIEW in the source database and access the VIEW in Qlik Replicate.

Hope this helps.

John.

Re: Join SAP HANA tables using source_lookup

Al_gar — Tue, 30 Jan 2024 02:07:57 GMT

Thanks @john_wang. Can you point out what log traces should I increase to troubleshoot why the task is failing with error message "The task stopped abnormally"?

Re: Join SAP HANA tables using source_lookup

deepaksahirwar — Tue, 30 Jan 2024 04:20:33 GMT

Dear @Al_gar ,

Welcome to Qlik Community forum and thanks for reaching out here!

The source_lookup function uses a value that you provide in an expression to find a corresponding value in a file or different table. It is a useful alternative to join operations, especially when you want to avoid null values or duplicate keys.

One possible reason for your error is that you are using the NO_CHACHING option, which means that the lookup table is not cached and the lookup function has to query the database every time it needs a value. This could cause performance issues and errors if the database connection is not stable or the table is not accessible.

To fix this error, you could try to use the CACHING option instead, which means that the lookup table is cached in memory and the lookup function can access it faster and more reliably. You can also specify the cache size and refresh interval to control how often the cache is updated.

To use the CACHING option, you need to modify your expression as follows:

source_lookup(‘CACHING’,‘sch’,‘Category’,‘category_name’,‘cat_id=?’,$cat_fkid,1000,60)

This means that the lookup table will be cached with a size of 1000 rows and a refresh interval of 60 seconds.

To answer your second question, yes, it is possible to join more than two tables using source_lookup function. You just need to use multiple source_lookup expressions in the Transform tab of the target table, one for each source table that you want to join. For example, if you want to join three tables: sch.Product, sch.Category, and sch.Supplier, you could use the following expressions:

Sales_Category = source_lookup(‘CACHING’,‘sch’,‘Category’,‘category_name’,‘cat_id=?’,$cat_fkid,1000,60) Supplier_Name = source_lookup(‘CACHING’,‘sch’,‘Supplier’,‘supplier_name’,‘sup_id=?’,$sup_fkid,1000,60)

This would add two columns to the target table: Sales_Category and Supplier_Name, with the values from the corresponding source tables.

I hope this helps you resolve the issue.Have a nice day! 😊

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the ans

wer.

Best Regards,

Deepak

Re: Join SAP HANA tables using source_lookup

john_wang — Tue, 30 Jan 2024 11:05:21 GMT

Hello @Al_gar ,

Depends on which component(s) lead the problem, you may try to set some components to Trace, or Verbose (if trace level still cannot provide enough information). In general please do not set all of the components to Verbose, it will make huge task log file and it's hard to read.

If problem is at source side:

-- For full load, set source_unload to Verbose

-- For CDC stage , set source_capture to Verbose

If problem is at target side:

-- For full load, set target_load to Verbose

-- For CDC stage , set target_apply to Verbose

or you can combine the components by Full Load, or CDC, for example source_capture & target_apply are used to troubleshoot CDC stage issues. Certainly you may set other components too depends on needs.

Hope this helps.

John.