topic Replicate data with dynamic query in Qlik Replicate

Replicate data with dynamic query

wguerrero — Thu, 04 May 2023 15:29:56 GMT

Now we are replicating data from oracle db onpremise to Mysql AWS RDS.

So, we have several tables with years of data and we want to replicate in a FULL MODE load only the last "x" years.

We don't want to put the date in "hard code" on table option (filter) so we want to use any kind of function to get the results described on the line up.

please, your help.

regards

Re: Replicate data with dynamic query

Dana_Baldwin — Thu, 04 May 2023 15:53:34 GMT

Hi @wguerrero

Please refer to these two knowledge articles and let us know if you have any questions:

Filter for last 90 days of data in Qlik Replicate - Qlik Community - 1880761

Filter for last 90 days of data in Qlik Replicate ... - Qlik Community - 1980009

Thanks,

Dana

Re: Replicate data with dynamic query

wguerrero — Thu, 04 May 2023 16:10:14 GMT

Will check and let you know.

Re: Replicate data with dynamic query

wguerrero — Wed, 10 May 2023 17:01:24 GMT

we checked the documentation and describe that is possible to do some things.

But we need some help with this query:

My query on oracle source is:

select *
from APP_EPS.PRE_LIQUIDACION_FACTURA
where FEC_CREACION>= add_months(trunc(to_date(SYSDATE, 'dd/mm/rrrr'), 'Year'), -108);

And we want to set up this query on the screen1 or screen2.

How can we translate this query on qlik replicate? and where should I place (please see attached screens).

thank you

Re: Replicate data with dynamic query

wguerrero — Thu, 11 May 2023 14:02:38 GMT

do you have any update?

Re: Replicate data with dynamic query

Dana_Baldwin — Thu, 11 May 2023 16:54:55 GMT

Hi @wguerrero

I suggest using the full load pass through filter since you are only running full load on this task, as it will filter on the source rather than selecting all rows and filtering on the Replicate server - it will be more efficient.

You would only need to specify the where clause portion of your query:

FEC_CREACION>= add_months(trunc(to_date(SYSDATE, 'dd/mm/rrrr'), 'Year'), -108);

in the full load pass through filter section of the screen.

You won't see this until you enable the feature in the repctl.cfg file as noted in the link I shared earlier:

Filter for last 90 days of data in Qlik Replicate - Qlik Community - 1880761

Thanks,

Dana

Re: Replicate data with dynamic query

wguerrero — Thu, 11 May 2023 17:40:38 GMT

ok Will set up and let you know.

thank you

Re: Replicate data with dynamic query

wguerrero — Thu, 11 May 2023 19:38:25 GMT

Hi,

we have done 2 things:

THING No 1:

One of them was test the line with function as per your request on file attached: screen_error_filter.png,

Why we are getting the error?

THING No 2:

And the second request is about parameter described. We are attaching the cfg file.png file in order you can help. The parameter described on your answer will have to added?

but, this one Will not affect another tasks already setup?

please your kindly help.

Re: Replicate data with dynamic query

Dana_Baldwin — Thu, 11 May 2023 20:59:06 GMT

Hi @wguerrero

You are on the Transform tab, not the Filter tab.

The field you want to put the expression in is called "Fullload Passthru Filter". You will not see this field until you have successfully enabled in the config file. After enabling it per the below steps, you need to hold down the control key on your keyboard while opening table settings.

To enable it, put a comma after the last "false" in your screen capture, paste in

"enable_passthrough_filter": true

after this line but before the closing curly bracket.

Then restart the Replicate services and it should be enabled.

Thanks,

Dana

Re: Replicate data with dynamic query

Heinvandenheuvel — Fri, 12 May 2023 14:01:27 GMT

@wguerrero - posting a picture of a notepad is counter productive (silly!) - in future please consider attaching the actual text file or use the </> edit option for 'Insert code sample' for small chunks of text like here. In either case folks can help you more easily and better by for example copying code and trying or providing edited code as solution.

- "do you have any update?" Please note that you are posing to a community forum with ZERO update obligations - best effort only. If you need something more formal you should submit a support ticket.

- Instead of the configuration option, which would affect every user, all tasks, you could consider to <CONTROL>-CLICK the table needing a pass Thru filter. Next select 'filter' and you'll see the <Fullload Passthru Filter> edit box available. Once you have done that once I recommend to export the task JSON, look for the filter provided and edit the JSON going forward.

- minro remark - why convert SYSDATE ? You do not need the to_date - it is a date!

SQL> select add_months(trunc(to_date(SYSDATE, 'dd/mm/rrrr'), 'Year'), -108) from dual; 01-JAN-14 SQL> select add_months(trunc(SYSDATE, 'Year'), -108) from dual; 01-JAN-14

Attached a picture of the <Fullload Passthru Filter> screen

hth, Hein.

Re: Replicate data with dynamic query

wguerrero — Fri, 12 May 2023 17:14:43 GMT

thank you all of you.

Hein, thank you for your response and advices. Will take in count for future help.

Let me comment my case.

We clic CTRL on the table and the double clic as per you said, the Passthru appears but after we try to reload the table shows us an error describing that we must to add the parameter on the config file, but we are afraid to do that, and the question is:

¿although we will put those parameter on the config file, no one table will be affect unless we put something on the PASSTHRU on that table?

Thank you again to both.

Re: Replicate data with dynamic query

Dana_Baldwin — Fri, 12 May 2023 18:03:50 GMT

Hi @wguerrero

Yes that is correct. Enabling the pass through filter will only affect tables that have a filter specified.

Regards,

Dana

Re: Replicate data with dynamic query

wguerrero — Mon, 15 May 2023 14:21:22 GMT

Hi,

thank you for your answers, Hein and Dana.

Hein, we have setup with CTRL + clic on the table on the text box you described. But when we just clic on RUN it shows us a message about we have to put the parameter on CFG file, but we are afraid that something happens with our another tasks.

But we have setup a workaround, (please check the attached), this action works well and send the data to the target without any problem. But we want to know what is your opinion about this method.

Please, your kindly help.

Re: Replicate data with dynamic query

Heinvandenheuvel — Mon, 15 May 2023 14:38:01 GMT

The issue with the workaround using a 'normal' filter action is the the row needs to be transferred to Replicate for it to decide. If 1 in a 100 rows is filtered out then that's fine. If 99 out of each 100 rows are filtered out then that could be a huge waste of resources. If the total resources used are minimal or dirt cheap than go ahead, but if the reources used are expensive/slow, then there is a point where you really want the filtering to be pushed down to the source such that no needles rows are pushed over the network and handled by Replicate. The Replicate level filtering would still be great for 'tweaking', final filters. Getting rid of more most rows to be filtered (more than 30% of all for example) is obviously best done on the source. The source might even have a index to help, avoiding reading rows which are not needed. Not important for 100 rows, critical for 100 million rows.

Hein.

Re: Replicate data with dynamic query

wguerrero — Tue, 16 May 2023 17:26:55 GMT

ok Thank you.

We will take in count your advice.

regards