topic Re: Trying to extract delta data from the table using the Unix Epoch Time in Qlik Replicate

Trying to extract delta data from the table using the Unix Epoch Time

RamBoopalam — Fri, 27 May 2022 19:07:48 GMT

In a table column in oracle it has Unix Epoch time, want to extract one day older data (delta data). How do I filter in Qlik Replicate in the filter condition having Epoch Time. Please advise

We have a table in Source and it has column which stores the time in Unix Timestamp (Epoch) and I want to get the delta data from the table, basically

Today = 1653652800000 - 5/27/2022

$LAST_REFRESH >= ( 1653566400000 - Epoch Time for 5/26/2022)
Ideally the above has to be in Filter condition for Source
$LAST_REFRESH >= (SYSDATE- 1 of Epoch Time value)

How can we dynamically extract the data on a daily basis? Please let me know.

Re: Trying to extract delta data from the table using the Unix Epoch Time

Michael_Litz — Fri, 27 May 2022 19:30:08 GMT

Hi RamBoopalam,

I see that you have opened a support case for this and I will take and work it with you.

Today = 1653652800000 - 5/27/2022

$LAST_REFRESH >= ( 1653566400000 - Epoch Time for 5/26/2022)
Ideally the above has to be in Filter condition for Source
$LAST_REFRESH >= (SYSDATE- 1 of Epoch Time value)

How can we dynamically extract the data on a daily basis? Please let me know.

Thanks,
Michael

Re: Trying to extract delta data from the table using the Unix Epoch Time

Steve_Nguyen — Fri, 27 May 2022 19:32:35 GMT

here is an article on timestamp filter that may help :

https://community.qlik.com/t5/Knowledge/Filter-for-last-90-days-of-data-in-Qlik-Replicate/ta-p/1880761

Re: Trying to extract delta data from the table using the Unix Epoch Time

RamBoopalam — Fri, 27 May 2022 19:42:49 GMT

Hi Steve,

Thanks a ton for the quick response, but in my case, the source table.columns which i am trying to extract has Epoch (Unix Time in Seconds) than date.

LAST_REFRESH is the field in Source and it's datatype is Numeric (19,0)

E.g.

Today = 1653652800000 - 5/27/2022

$LAST_REFRESH >= ( 1653566400000 - Epoch Time for 5/26/2022)
Ideally the above has to be in Filter condition for Source
$LAST_REFRESH >= (SYSDATE- 1 of Epoch Time value)

Re: Trying to extract delta data from the table using the Unix Epoch Time

john_wang — Sun, 29 May 2022 02:25:20 GMT

Hello @RamBoopalam , copy @Steve_Nguyen @Michael_Litz ,

There should be many different expressions to present 'yesterday' (only date part, means from 0 hour 0 min 0 sec) by Unix Epoch. Because your data contains milliseconds part, one sample for (SYSDATE- 1 of Epoch Time value) is:

(strftime('%s', date('now','localtime')) - 86400)*1000

Additional comments: 1 day = 86400 seconds; Convert to milliseconds by multiplied by 1000.

The screen copy of the demo:

Hope this helps.

Regards,

John.

Re: Trying to extract delta data from the table using the Unix Epoch Time

RamBoopalam — Tue, 31 May 2022 13:06:19 GMT

Thanks a ton John, it worked. Really appreciate your help

Re: Trying to extract delta data from the table using the Unix Epoch Time

john_wang — Wed, 01 Jun 2022 00:05:42 GMT

Glad to hear that @RamBoopalam , thanks for your feedback.