incremental load with timestamp

[narender123]

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 )

like

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

[fkeuroglian]

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

[sergio0592]

Hi,

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

Regards

[sergio0592]

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’)

[narender123 · Accepted solution]

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

[narender123]

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

[fkeuroglian]

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