Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Team,
I am using qlik rest apis to do scheduled load from huge source tables to targets. Today, I am using reload apis, so simply truncating target table and loading everything from source to target table.
But we wanted to limit the data transfer everytime, so including some dynamic filters or conditions. eg load only rows where date is today's date. Could you please advise how it can be managed programmatically.
Thanks in advance.
>> But we wanted to limit the data transfer everytime, so including some dynamic filters or conditions.
It'll be tricky. For starters, assuming you want to keep what was already loaded, you need to change the task setting to "Target Table Preparation - If target table already exists: - Do nothing". This is a task-wide setting, not available per-table (IMHO it should be)
Next if you wanted to use a filter. T is relatively easy and can indeed be based on a dynamic expression with current date or even a (cached) target_lookup with some start or end value. However, using filters Replicate will still READ all the source rows, just write less.
Replicate also offers 'passthru' (Pass-through ) filters. Those are SOURCE SQL expression and can use things like 'create_date > sysdate - 1'. Search this forum for passthru for details.
Finally, how about loading from a VIEW on the source instead of the main table. That gives you maximum flexibility on the filter expression.
hth,
Hein
Hello team,
If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.
Regards,
Sushil Kumar
Dear @Learner1 ,
One way to achieve this is to use the `WITH CONNECTION` statement in your script, which allows you to modify the URL or other parameters of your REST connection dynamically.
For example, if you want to load only the rows where the date is today's date, you can create a variable that holds the date value in the format that your API expects, and then use it in the URL of your REST connection. Here is a sample script:
//sql
// Define a variable that holds the date value in YYYY-MM-DD format
LET vDate = Date(Today(), 'YYYY-MM-DD');
// Connect to your REST connection
LIB CONNECT TO 'MyRESTConnection';
// Load the data from the API using the date variable in the URL
RestConnectorMasterTable:
SQL SELECT
// Your field list here
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (
URL "https://myapi.com/data?date=$(vDate)"
You can also use other variables or expressions in the `WITH CONNECTION` statement, such as query headers, pagination, authentication, etc. For more details and examples, you can refer to the [Qlik help page] on REST connector scripting.
If our response has been helpful, please consider clicking "Accept as Solution".
This will assist other users in easily finding thea answer
Regards,
Deepak