topic Pull Random Sample of Table Records in Qlik Replicate

Pull Random Sample of Table Records

JamieKay — Thu, 13 Feb 2025 21:11:56 GMT

We are trying to load random rows from the source table in Oracle to do validation of what is in our target. Is there a way to do that?

A query in Oracle would look something like:

SELECT * FROM <TABLE>

SAMPLE(<sample percentage>)

Thank you!

Re: Pull Random Sample of Table Records

john_wang — Fri, 14 Feb 2025 05:42:30 GMT

Hello @JamieKay ,

It looks like you are trying to replicate random rows using a Full Load ONLY task, which may not be meaningful for the CDC (Change Processing) stage. If my understanding is correct, here are a few possible approaches:

Filter Random Rows Using a Primary Key (PK) Range
- You can retrieve random rows by applying a filter based on the PK random values(or other column values).
- Example:
  
  WHERE PK BETWEEN TRUNC(DBMS_RANDOM.VALUE(1, 30000)) AND TRUNC(DBMS_RANDOM.VALUE(1, 30000)) * 2
- Add this condition as a passthrough filter.
Use a View to Select Random Rows
- You can define a VIEW that selects a random subset of rows from the base table and then include the view in a Full Load ONLY task.
- Example:
  - Select a percentage of rows:
    
    SELECT <column_list> FROM <table> SAMPLE(10);
  - Select a fixed number of rows:
    
    SELECT <column_list> FROM <table> ORDER BY DBMS_RANDOM.VALUE FETCH FIRST 10 ROWS ONLY;

Hope this helps!

John.

Re: Pull Random Sample of Table Records

JamieKay — Fri, 14 Feb 2025 21:37:54 GMT

That worked - thank you!!!

Can I do the same using a Record Selection Condition?

What I am wanting is 10 records randomly chosen where <some date column> >= to_char(current_date - 180, 'YYYY-MM-DD').

Thank you again, @john_wang!

Re: Pull Random Sample of Table Records

john_wang — Mon, 17 Feb 2025 04:35:36 GMT

Hello @JamieKay ,

Thanks for the update. Yes, you can; however, the passthrough filter offers the best performance since it eliminates unnecessary rows before they are retrieved from the source database. Please see my comments on the comparison of the three filters.

Regards,

John.

Re: Pull Random Sample of Table Records

JamieKay — Mon, 17 Feb 2025 17:40:05 GMT

Thank you @john_wang!

Re: Pull Random Sample of Table Records

john_wang — Tue, 18 Feb 2025 01:07:45 GMT

Thank you so much for your support @JamieKay