topic Re: incremental load with timestamp in QlikView

incremental load with timestamp

narender123 — Sat, 16 Nov 2024 03:07:46 GMT

Hi All,

I am doing incremental load in table which is a table of oracle db.

Table has a timestamp field updated_date having value "07-07-2014 10:08:11.238000" having format "DD-MON-RR HH:MI:SSXFF AM" in oracle.

I need to take this time stamp to do incremental load in qlikview.

I am facing problem as mention below 2 points.

1) I have to calculate max(time stamp) and pass that into a variable let say varupdatedate .

2) During apply where clause in oracle table updated_date>$(varupdatedate )

sql select * from db.table1 where datefield>variable;

I am facing problem in timestamp because records having difference based on mili seconds(ms).

Thanks,

Narender

Re: incremental load with timestamp

fkeuroglian — Wed, 10 Jul 2019 16:09:23 GMT

Hi How are you?

Your incremental load in qlik is one time for each day?

Can you take out the milisecond and only have the date format? apply some DATE functions or FLOOR functions to have the normal date format? "07-07-2014" for example?

Why you can not use this format?

1) I have to calculate max(time stamp) and pass that into a variable let say varupdatedate .

You have to calculate the max? can you use the today() function and use this value to set the variable?

Hope Help

Fernando

Re: incremental load with timestamp

sergio0592 — Wed, 10 Jul 2019 17:26:33 GMT

Hi,

You have to use peek() for fetch value in a variable. I'll write the exact syntax tomorrow morning.

Regards

Re: incremental load with timestamp

sergio0592 — Thu, 11 Jul 2019 07:20:32 GMT

So, try with :

T_MAX: load Timestamp(max(UPDATED_DATE)) as max_DH Resident TABLE; LET v_MaxDate = peek('max_DH'); SQL SELECT x x FROM Table X WHERE UPDATED_DATE >= TO_DATE('$(v_MaxDate)','DD-MON-RR HH:MI:SSXFF AM’)

Re: incremental load with timestamp

narender123 — Thu, 11 Jul 2019 08:00:44 GMT

Hello Fernando,

I am Good. Thanks.

How are you?

No I can not take out the milisecond from date as I have multiple records which are inserting with the difference of millisecond. like

If i would take out millisecond from date then max date with second would be 07-07-2014 10:08:11 (assume) and this date may be reloaded during milliscond 347000. but you know there are also more date with the same second but different millisecond. So during grater then max date condition in next step will ignore for the other millisecond. That is why i want to take the millisecond record in date field.

But I used alternative way with different logic.

1) load date(max(TIMESTAMP(op_date_time,'DD-MM-YYYY'))-1) as maxdate

2) Pass this into variable let say varmaxdate

3)load * where to_date(to_char(op_date_time,'DD-MM-YYYY'),'DD-MM-YYYY')>to_date('$(varmaxdate)','DD-MM-YYYY')

after that applied update and delete steps.

Update step will not take same day data 2 times.(which is creating due to (maxdate-1) condition) .

I hope , this is also the right way. what you say about this?

Thanks,

Narender

Re: incremental load with timestamp

narender123 — Thu, 11 Jul 2019 08:06:17 GMT

Hi sergio.

Thanks for your reply.

Previously i applied the same thing as you suggested here but did not get success as this was not working:

>TO_DATE('$(v_MaxDate)','DD-MON-RR HH:MI:SSXFF AM’)

I replied to Mr. Fernando in same conversation. Please see my comment , I have summarized my answer to resolve this issue.

Thanks,

Narender

Re: incremental load with timestamp

fkeuroglian — Fri, 12 Jul 2019 13:15:00 GMT

Hi how are you?

it seems ok! would work ok

"after that applied update and delete steps", this is the important part

Let me know if you need help!

Fernando