topic Re: Source Tables have dynamic names how can we process them? in Qlik Replicate

Source Tables have dynamic names how can we process them?

jonna — Fri, 25 Feb 2022 16:25:09 GMT

Hi Replicate friends

The source system we are collecting data from stores its records into a NEW TABLE EVERY DAY - this is because of the sheer volumes being processed. The tables are named as follows: "travel_data_yyyymmdd" with one for every day.

Is there a way to create a single task that can read these table names dynamically? Perhaps something along the lines of using wildcards??

Thanks in advance

ianjonna

Re: Source Tables have dynamic names how can we process them?

lyka — Fri, 25 Feb 2022 16:30:54 GMT

Hello,

You can use the "Include" option under Table Selection.

Example

Source Tables

Schemas:

Table:

Table Selection Patterns

Pattern	Load Order
Include dbo.travel_data_%

Thanks

Lyka

Re: Source Tables have dynamic names how can we process them?

jonna — Fri, 25 Feb 2022 16:34:51 GMT

Lyka, that is awesome, thanks very much for your help 😁

Re: Source Tables have dynamic names how can we process them?

Heinvandenheuvel — Fri, 25 Feb 2022 17:38:10 GMT

@jonna In a recreate of the same topic with no further details you indicate the solution thus far did not tick all the boxes.

My recommendation is to delete that new topic, continue with a reply here explaining in detail why the solution was insufficient. Remember, we are not mind-readers. Help us help you.

Note 1: The indicated solution "travel_data_%" is slightly misleading suggesting that the match is for "travel" followed by a widcard character followed by "data" followed by underscore followed by anything. It matches "travel_data_yyyymmdd" but also "travelXdataXyyyymmdd"

The underscore character is a single character wildcard and thus the solution provided is almost equivalent to "travel%data%". If the underscore is required, then you must provide it as a single character in a class: [_]

Note 2: Many application with source tables find that it seems a good idea at a time, but it really sucks! It may make some sense on a resource constraint OLTP source, but on the target (BI system) a single table holding 'travel_data' for all dates may well be preferable. If so, create a GLOBAL TRANSFORMATION to remove the '_yyyymmdd' from the source name to generate a target. Unfortunately the source table name is NOT available as a variable, so you cannot add a computed column to indicate the origin in the combined target table. Still, there is a likely a date column already to satisfy that need.

Note 3: You may find that while global replace is neat, when all is said and done you are better of just generating the JSON for 100+ anticipated tables, and add a helper column with origin if needed. This is spcially true if changes to the dated source tables are limited in date scope like only allowed within 30 days of creation. Generate a fresh task every 30 days for 30 days backwards, and 70 days future. Stop and resume.

Good luck!

Hein

Re: Source Tables have dynamic names how can we process them?

jonna — Fri, 25 Feb 2022 17:48:52 GMT

Good afternoon Hein

Very grateful for your help on this one, it seems you have hit the nail right on the head in relation to the specific help I was looking for. I will do some testing right now and come back to update my entry in here.

Many thanks (again)

ianjonna