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?
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.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
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"?
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:
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:
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.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
