topic Incremental reloading in QlikView

Incremental reloading

Fri, 22 Feb 2013 07:30:02 GMT

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.

Re: Incremental reloading

sushil353 — Fri, 22 Feb 2013 07:43:09 GMT

can u load sample application or data

Re: Incremental reloading

Fri, 22 Feb 2013 07:44:37 GMT

post script with those 3 steps

Re: Incremental reloading

somenathroy — Fri, 22 Feb 2013 07:50:40 GMT

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

Regards,

som

Re: Incremental reloading

Fri, 22 Feb 2013 08:36:14 GMT

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?

Re: Incremental reloading

manideep78 — Fri, 22 Feb 2013 08:45:35 GMT

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

Re: Incremental reloading

Fri, 22 Feb 2013 09:39:18 GMT

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.

Re: Incremental reloading

whiteline — Fri, 22 Feb 2013 18:06:08 GMT

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.

Re: Incremental reloading

Mon, 25 Feb 2013 04:30:47 GMT

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.

Re: Incremental reloading

rwunderlich — Mon, 25 Feb 2013 05:19:57 GMT

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

Re: Incremental reloading

Fri, 01 Mar 2013 06:26:08 GMT

Hello Rob,

Your solution did work successfully.

Thanks a lot!!!

Re: Incremental reloading

Thu, 07 Mar 2013 06:23:37 GMT

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.

Re: Incremental reloading

rwunderlich — Thu, 07 Mar 2013 18:21:38 GMT

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

-Rob

Re: Incremental reloading

Fri, 08 Mar 2013 04:40:09 GMT

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.

Re: Incremental reloading

rwunderlich — Sat, 09 Mar 2013 00:51:34 GMT

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

Re: Incremental reloading

Mon, 11 Mar 2013 04:02:56 GMT

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

Re: Incremental reloading

rwunderlich — Mon, 11 Mar 2013 05:33:53 GMT

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

Re: Incremental reloading

Mon, 11 Mar 2013 06:20:11 GMT

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.

Re: Incremental reloading

rwunderlich — Mon, 11 Mar 2013 14:04:15 GMT

What version of SQL Server are you using?

-Rob

Re: Incremental reloading

Tue, 12 Mar 2013 04:19:10 GMT

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