topic Creating a task to just count source table records in Qlik Replicate

Creating a task to just count source table records

RaySRI1 — Fri, 02 Sep 2022 20:56:56 GMT

I don't have direct access to the source tables so I would like to create a replicate task to just count the records in the source tables. Can I create task to just count the source table records? I would prefer not to transfer gb's of data, but can I create a task to move data to a test table but not impact other tasks using the same source table? Could I create a filter that would prevent data from being transferred but yet give me a count in the logs or on the monitor on the total records scanned? Any other ideas?

Thanks

Re: Creating a task to just count source table records

lyka — Fri, 02 Sep 2022 20:54:45 GMT

Hello,

You can try this - run a full load only task using a NULL target endpoint

Please find the steps below to create NULL target endpoint,
1. hold down CTRL key, click on "Manage Endpoint Connections".
2. click "New Endpoint Connection".
3. set the Role to "Target"
4. In the "Type" list, you should find a type called "NULL Target (>/dev/null)"

Then please use this NULL Target endpoint in your task.

Hope this helps

Thanks

Lyka

Re: Creating a task to just count source table records

RaySRI1 — Fri, 02 Sep 2022 21:13:07 GMT

That works! thank you very much for the quick response. Weekend is starting on a high note.

Re: Creating a task to just count source table records

lyka — Fri, 02 Sep 2022 21:15:02 GMT

Hi @RaySRI1

im glad it worked out! have a great long weekend 🙂

Thanks

Lyka

Re: Creating a task to just count source table records

Heinvandenheuvel — Tue, 06 Sep 2022 03:18:30 GMT

The solution you chose is still transferring all rows from the source to Replicate, only to ignore them after that. That does not seem to address your stated objective " I would prefer not to transfer gb's of data,"

Is there a single row (system) table in the source DB, not part of the Replication? See if you can solve this using a bunch of source lookups using that single row in <some_schema>.<some_table_with_1_row> as the table to load in your count task but dropping all existing columns for that table and adding a fresh column for each table for which you need a count using a source_lookup as expression.
Each of those (many) expressions would look like:
Added column name = <own>_<table>_<count>
Expression = source_lookup(0,'<some_schema>','<some_table_with_1_row>',
'(SELECT COUNT(1) FROM <own>.<table>)','1=1')

Can you create a helper table in the source database such as: create table table_row_counts ( owner_name varchar(30), table_name varchar(30));
Now add a single column with a single expression to count for each source row: source_lookup(0,$OWNER_NAME,$TABLE_NAME,'COUNT(1)','1=1')

Both methods tested, and they work for me for an Oracle source.

No data, other than the counts transferred (and the owner/tablename columns for the second solution).

Hein.

Re: Creating a task to just count source table records

john_wang — Tue, 06 Sep 2022 14:09:43 GMT

Hello @RaySRI1 , copy @lyka , @Heinvandenheuvel ,

if the source DB is Oracle then Materialized View can be archive that by the most efficient way. the key problem is if it's allowed to create such a view in PROD system.

Regards,
John.