Hi All,Is there any way to put data pool parameters for just rolling 12 months so that it can extract data for last 12 months only every time ?Can we define such parameter for extraction and what will be the syntax?

Hi Shruti,

you can achieve it by using the dynamic parameter and operation type FIND_MIN and combined with a SQL transformation to delete older data than 12months in those tables. This way, when the extraction job starts, it will use the MIN value date of the table.

Hope it helps.

Best,

Gabriel

Thanks Gabriel for the response. In my case the dates are in Julian format.

Do I need to define Table and column value also? As this parameter will be used to extract data from various JD Edwards table for rolling 12 months.

"combined with a SQL transformation to delete older data than 12months in those tables" - Can you provide more details on this part? Do you mean to write a new SQL transformation to delete older data than 12months in required tables only? If yes, this we can do.

Problem here is we can reduce the APC % by deleting in transformation but the execution time will remain high in extraction.

So, just wanted to confirm again if this can be achieved just at extraction layer itself without writing any SQL transformation.

Hi Shruti,

correct. You would only need this dynamic parameter for the full load (delta load would remain the same. Indeed you would need to extract to them drop the older data, but APC is also not calculated live, so you should face no problem.

Minvalue should work for Julian date format as well.

If you want to reduce execution time, excluding an extra few 1-2 days will not be the main improvement opportunity.

Best,

Gabriel

hello Gabriel

I thought you could also use a formula in the parameters like

ADD_DAYS ( ROUND_DAY(TODAY()), -180 )

to only select 180 days but i do not get it working because I can't put this formula in the parameter/task

any suggestions?

Hi @geoffrey.craps12​ ,

unfortunately, this is not possible to do in the parameters.

You could achieve it with a delta load (configure it normally by using the max date for example) and on the transformation you drop the data older than 180 days. For the first part, if you have doubts how to establish it, there is a fairly good documentation in the Help Resources Page about Delta Loads.

For the second, I shared one script sample in another post

https://www.celopeers.com/s/question/0D507000018vHbzCAE/what-is-the-easiest-way-people-have-found-to-purge-data