Qlik Replicate | Using filter condition on table

[UCHIHA_ITACHI]

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

[john_wang]

Hello @UCHIHA_ITACHI ,

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.

[john_wang]

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.

[UCHIHA_ITACHI]

@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

[DesmondWOO]

Hi @UCHIHA_ITACHI ,

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

[UCHIHA_ITACHI]

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.

[john_wang]

Hello @UCHIHA_ITACHI ,

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.

[DesmondWOO]

Hi @UCHIHA_ITACHI ,

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

[Heinvandenheuvel]

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.

[UCHIHA_ITACHI]

@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