Incremental reloading

[#1]

ello,

I am working on Incremental reloading.

The procedure I followed for the same is:

1. 1st time full load and store data in qvd with timestamp field.

2. Next time I get the max value of the timestamp field from the qvd.

3. read new or updated records from database based on the timestamp field.

But here in step 2 I dont get the correct max value.

Please can anyone help me on this issue of how to get the max timestamp value from qvd.

[#2]

Hello Rob,

Your solution did work successfully.

Thanks a lot!!!

[#3]

Hello Rob,

The solution worked correctly on my local machine.

But on the server, I am facing problem in getting the max timestamp value.

what I have done is:

1. Get the timestamp from database and convert it into int using convert(int, timestamp) in SQL query itself.

2. store the same converted timestamp in QVD.

3. Next time get the max from the QVD and store it in a variable $(maxtimestamp)

4. Then get the records from database with the condition convert(int, timestamp) > $(maxtimestamp).

But I found on the server that sometimes the timestamp is getting converted into -ve values and then QVD is not returning the correct max value.

Please guide me what to do now.

[rwunderlich]

Try using the qlikview functions in my example instead of the SQL convert() function.

-Rob

[#5]

Rob,

I had used the qlikview function as mentioned in your example.

So the num(num#(right(timestamp,12) '(HEX')) as timestamp

returned me 1356646010 for the hex value 0x0000000050DCC67A.

this is my max hex value as per SQL. Qlikview returnes correct above max timestamp as 1356646010.

I stored this value in a variable.

No issue in above section. Till this point all is working fine.

But now to get the records greater than 0x0000000050DCC67A, I wrote the sql query as

WHERE timestamp > $(maxtimestamp).

But my $(maxtimestamp) has value as 1356646010. So I converted to int as  WHERE convert(int, timestamp) > $(maxtimestamp).

If not to convert to int then how to get new records from DB?

Please help me on this ASAP.

My qlikview version is 10.

[rwunderlich]

You hae already converted to an int with the num(num#()). There should be no need to use a convert(). In my experience, WHERE timestamp > $(maxtimestamp) should be fine.

-Rob

[#7]

But Rob,

timestamp field in table is having values in hex format

and the variable $(maxtimestamp) is having values in int format.

Then how the where condition will work?

eg: where 0x00000000838D302D > -2087899091

[rwunderlich]

Hex is just the ouput format that SQL returns to you. Have you tried my suggestion of using the int value in the WHERE clause? It works for me.

-Rob

[#9]

Rob,

I didnt knew that its just a output format of SQL.

so I was converting  in query also.

I tried now on my local machine, with WHERE timestamp > $(maxtimestamp) and it is working fine.

But when I tried on the server, it is not working.

I tried the following query on SQL editor:

my actual query in qlikview is where timestamp > $(maxtimestamp), but for testing I have written below query.

select * from tble

where timestamp = 0x0000000083B6B8D7  --- works fine. returns result

select * from tble

where timestamp = 2209790167 -  returns error

Error converting data type timestamp to numeric.

[rwunderlich]

What version of SQL Server are you using?

-Rob

[#11]

Rob,

On my local machine, SQL server version is SQL Server 2008 R2 - 10.50.1617.0

and on the server it is SQL Server 2008 R2 - 10.50.1600.1.

One more observation, not for all the values in timestamp column, I get this error.

for the value 2000000302 I get the output, but for 2209790167 I get the error.

You can also check at your end if you have any table having timestamp field with following query (replacing TABLE with your tablename)

select * from TABLE

where timestamp = 2209790167