Replicate data with dynamic query

[wguerrero]

Hi

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

[Dana_Baldwin]

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

[wguerrero]

Hi

Will check and let you know.

[wguerrero]

Hi

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

[wguerrero]

Hi

do you have any update?

[Dana_Baldwin]

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

[wguerrero]

ok Will set up and let you know. 

thank you

[wguerrero]

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.

[Dana_Baldwin]

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

[Heinvandenheuvel]

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