How to do dynamic conditional load programmatically using qlik rest api

[Learner1]

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.

[Heinvandenheuvel]

>> 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

[SushilKumar]

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

[deepaksahirwar]

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