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.

[sushil353]

can u load sample application or data

[#3]

post script with those 3 steps

[somenathroy]

use function :  Timestamp(Timestamp#(Max(field))

Regards,

som

[#5]

1. LET TimeStamp = 0;

2. LET vQvdExists = if(FileSize('$(vQvdPath)' & '$(vQvdName)') > 0, -1, 0);

3. IF $(vQvdExists) then

4.   [MaxData]:

5.   LOAD timestamp FROM $(vQvdPath)$(vQvdName) (qvd);

6.   LOAD MaxString(timestamp) as Mxtimestamp Resident MaxData;

7.   LET Maxtmstmp=Replace(Left(FieldValue('Mxtimestamp', 1), 2), '00', '0x') & Mid(FieldValue('Mxtimestamp', 1), 1);

8. ENDIF    

9. [TblData]:

10.LOAD

11.PK,field1, field2, timestamp as timestamp;

12.SQL SELECT * FROM tblname

13.where timestamp > $(Maxtmstmp);

14.IF NoOfRows('TblData') > 0 then

15.  concatenate($(vTableName))

16.  LOAD * FROM $(vQvdPath)$(vQvdName) (qvd)

17.  WHERE Not Exists(PK);

18.  STORE $(vTableName) into $(vQvdPath)$(vQvdName);

19.ELSE

20.  LOAD * FROM $(vQvdPath)$(vQvdName) (qvd);

21.ENDIF

timestamp is having values as "0x0000000050548392"

But qlikview stores it as "000000000050548392".

so in line no 7 i have replaced x with 0.

Now I have problem in line 5,6.

It does not return me correct max value.

how to get the correct max value of timestamp?

[manideep78]

Try this,

Time(Frac(Max(Timestamp#(Time1,'DD/MM/YYYY hh:mm:ss'))))

The format of your Records in time filed and Second parameter in TimeStamp#() should be same(Bold Format).

For more understanding of Date and Time functionality go through this blog :

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

[#7]

the data in timestamp field is not actually datetime field.

the datatype is timestamp which stores binary values.

so the Timestamp# function dont work. I trieds using it but it returns null value.

[whiteline]

1) Timestamp# converts string-timestamp value of corresponding format (second parameter) into dual value that has number and text representation.

2) MaxString is not similar to Max finction for numeric values (look at help).

To convert string value into dual (i.e. numeric) value use Num# function with a corresponding format parameter.

Then you can use Max as aggregation function to get maximum timestamp value.

Why do you use "0x" as a prefix for timestamps and store them as texts ?

It's much easier just store numeric value and change the format in a GUI for user interface.

[#9]

Hello,

The value in timestamp field in database is 0x0000000050D7AF4F.

But qlikview stores it as 000000000050D7AF4F in QVD. replaces x with 0.

so while reading from QVD, i am replacing the 0 with x.

Now regarding getting the max value:

The max value in DB is 0x0000000050D7AF4F.

But after reading max value from QVD I get 000000000050548392 as max value.

So I want to know how can I get the correct max value.

Now I tried as

Max(Num#(Timestamp#(Maxtmstmp)))

but still incorrect value.

Please guide me how to do it if still I am going wrong somewhere.

[rwunderlich · Accepted solution]

As you've figured out, the SQL Server "timestamp" column type has nothing to do with dates and times. It's an automatically incrementing binary number that indicates a row has changed. In later versions of SQL Server (2008?) the column type was renamed from "timestamp" to "revision", but it's the same thing.

The way I handle these is to convert them to decimal in a preceeding load. I'm sure there's a SQL function to do it as well.

LOAD

  PK,

  field1,

  field2,

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

;

SQL SELECT * FROM tblname;

The right(x,12) is required because the num#() won't parse the 16 digit hex string. That takes the right 12 digits which should be enough.

When doing the incremental, you can just load the max(timestamp) and put it in a variable. Use the variable as is in the SQL WHERE

WHERE timestamp > $(maxtimestamp)

there is no need to convert it back to a hex representation.

-Rob

http://robwunderlich.com