Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
SreehariPV
Contributor III
Contributor III

Qlik Replicate | Using filter condition on table

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

Labels (4)
1 Solution

Accepted Solutions
john_wang
Support
Support

Hello @SreehariPV ,

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

thanks,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

18 Replies
john_wang
Support
Support

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:

john_wang_0-1712299158435.png

Then you add more ranges value like:

john_wang_1-1712299199861.png

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.

 

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
SreehariPV
Contributor III
Contributor III
Author

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

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

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
SreehariPV
Contributor III
Contributor III
Author

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

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
DesmondWOO
Support
Support

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

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Heinvandenheuvel
Specialist III
Specialist III

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.

SreehariPV
Contributor III
Contributor III
Author

@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