topic Re: Qlik Replicate | Using filter condition on table in Qlik Replicate

Qlik Replicate | Using filter condition on table

SreehariPV — Fri, 05 Apr 2024 06:28:29 GMT

Hi team,
I have a doubt regarding creating filter conditions for a column, in the attached image I have used two filter condition, I'm having a requirement were I want to use more number of filters that maybe in thousands(using python API QEM) , is it possible or can I know whats the maximum number of filters I can use.

Thankyou
Regards
Sreehari

Re: Qlik Replicate | Using filter condition on table

john_wang — Fri, 05 Apr 2024 06:46:00 GMT

Hello @SreehariPV ,

Thanks for reaching out to Qlik Community!

Yes, if you are adding more ranges value to the same column filter, the their relationship is OR. let's say you add a column , ID2 in the filter:

Then you add more ranges value like:

Qlik Replicate will compose below query and send to source endpoint DB for execution:

2024-04-05T14:35:32:439241 [SOURCE_UNLOAD ]T: Select statement for UNLOAD is 'SELECT "ID","ID2","NAME","INVID" FROM "SCOTT"."TESTINVISIBLE4" WHERE (("ID2" = +1.0) OR ("ID2" = +2.0))'

You may set SOURCE_UNLOAD to Trace and you will see what operations Qlik Replicate is doing now. Adding more range values are supported in Qlik Replicate, unless the composed SQL string is too long to a specific database.

Hope this helps.
John.

Re: Qlik Replicate | Using filter condition on table

john_wang — Fri, 05 Apr 2024 06:54:21 GMT

BTW, I'm not sure you have to input the thousands of individual values one by one, or you may input a range, eg BETWEEN, or Greater than etc.

Re: Qlik Replicate | Using filter condition on table

SreehariPV — Fri, 05 Apr 2024 07:35:59 GMT

@john_wang or else can I give it like : for example I have five values for a column
COL1
001
002
003
004
005

instead of giving individual ones like
Equal To = 001
Equal To = 002
...
...
Equal To = 005

Is it possible to give
Equal To = 001,002,003,004,005

Re: Qlik Replicate | Using filter condition on table

DesmondWOO — Fri, 05 Apr 2024 07:54:43 GMT

Hi @SreehariPV ,

Please check following steps if they help:
1) In the task setting, add 2 values in your filter.
2) Save task
3) Export task to a json file
4) Edit exported json file, search 'filter_columns'
5) Update 'ranges', for example:

"column_name": "id",
"ranges": "1,2,3,4,5,7,11"

6) Save your file
7) Import JSON file back to Replicate.

Regards,
Desmond

Re: Qlik Replicate | Using filter condition on table

SreehariPV — Fri, 05 Apr 2024 09:08:38 GMT

Hi @DesmondWOO , I have done the same what you have replied above, I only have a question that above I have mentioned 5 values , can I mention 'n' number of values or is there any limitations, like for example the limit is say '100' like in your example its id you have given "1,2,3,4,5...till 11' so only till 100 id's we can give in range field.

Re: Qlik Replicate | Using filter condition on table

john_wang — Fri, 05 Apr 2024 09:14:53 GMT

Hello @SreehariPV ,

You can input the values as "1,2,3,4,5,6" , then Replicate run it as:

2024-04-05T17:12:30:184644 [SOURCE_UNLOAD ]T: Select statement for UNLOAD is 'SELECT "ID","ID2","NAME","INVID" FROM "SCOTT"."TESTINVISIBLE4" WHERE (("ID2" = +1.0) OR ("ID2" = +2.0) OR ("ID2" = +3.0) OR ("ID2" = +4.0) OR ("ID2" = +5.0) OR ("ID2" = +6.0))'

thanks,

John.

Re: Qlik Replicate | Using filter condition on table

DesmondWOO — Fri, 05 Apr 2024 11:58:24 GMT

Hi @SreehariPV ,

I've tried 20000 values: from 1 to 20000, my task still works fine. I am not sure the limitation of Replicate but I think 20,000 values should be large enough. In the event that you encounter a limitation issue, feel free to create a support ticket.

Regarding other limitations, you should take into account the length of the SQL statement that your database supports.

Regards,
Desmond

Re: Qlik Replicate | Using filter condition on table

Heinvandenheuvel — Fri, 05 Apr 2024 13:38:47 GMT

At some point, as the number of ranges get bigger and bigger, you may look at using a VIEW at the source for full-load. If CDC is also in play then you could start to consider using source_lookup to see if the value is or is not in a look-aside table on the source. source_lookup can be expensive involving a trip back to the source for each row, but if the are lots of the same values being looked up, then the lookup cache may make it attractive. The table being looked up could be the same as used in the full-load view.

fwiw,

Hein.

Re: Qlik Replicate | Using filter condition on table

SreehariPV — Fri, 19 Apr 2024 12:30:11 GMT

@DesmondWOO I have tried with 20000 values, but the application got hanged, only when I deleted the task which had those filters, I was able to access the application properly

Re: Qlik Replicate | Using filter condition on table

SreehariPV — Fri, 19 Apr 2024 12:31:09 GMT

@Heinvandenheuvel Hi, we have considered this, but we have limited source access. So we didn't continue with this approach

Re: Qlik Replicate | Using filter condition on table

john_wang — Sat, 20 Apr 2024 03:15:44 GMT

Hello @SreehariPV ,

Would you please elaborate the necessary of 20000 individual values rather than a range?

thanks,

John.

Re: Qlik Replicate | Using filter condition on table

DesmondWOO — Mon, 22 Apr 2024 01:45:03 GMT

Hi @SreehariPV ,

Did you encounter hanging issue while defining a task or during task execution? What is your source database?

Regards,
Desmond

Re: Qlik Replicate | Using filter condition on table

SreehariPV — Mon, 06 May 2024 12:48:31 GMT

The client had a requirement where they wanted to fetch only particular records( eg: 5 days of a month, it does'nt have to the same days in a week) based on two filter columns, one is a date field and the other is an id column. So adding them maually is an issue so I'm using QEM's python API

Re: Qlik Replicate | Using filter condition on table

SreehariPV — Mon, 06 May 2024 12:51:46 GMT

The hanging issue was while opening the task and viewing the filters, the whole app gets stuck, I'm yet to run the task because I'm getting an error using the QEM's python API.
Source: Oracle
Target PostgreSQL

Re: Qlik Replicate | Using filter condition on table

Heinvandenheuvel — Tue, 07 May 2024 02:45:52 GMT

Time to face the music. It's just not a workable design. Tell your customer.

If they have to add a filter ID, with the proposed design they have to engage a replicate person instead of an DB or business analyst person, then generate a new task, import it, stop and start the task, Too risky.

Only half reasonable solution is a filter ID lookup table on source or target. That way the business can maintain it themselves and the task can keep on running. There will be a performance price of course.

Hein.

Re: Qlik Replicate | Using filter condition on table

john_wang — Tue, 07 May 2024 01:21:55 GMT

Totally agree with @Heinvandenheuvel , utilizing VIEW(s) greatly enhances task design simplicity and performance.

John.

Re: Qlik Replicate | Using filter condition on table

SreehariPV — Tue, 07 May 2024 06:44:04 GMT

While using run_task() what should I pass as payload, is it a json string or something else, what exactly should I pass to

AemRunTaskReq which is a payload parameter

Re: Qlik Replicate | Using filter condition on table

john_wang — Tue, 07 May 2024 07:03:49 GMT

Hello @SreehariPV ,

Would you please open a new article for the new issue? It's much easier for trace.

thanks,

John.