topic Incremental Load problem in QlikView

Incremental Load problem

Mon, 13 Aug 2012 21:47:59 GMT

I have been struggling with getting incremental load to work with SQL 2005. Our ERP software stores the changedate and then the changetime in seconds since midnight. I used CAST to convery this into a single decimal field that I can compare with Qlikview.

No matter what I try I have not been able to get this to work. Can anyone see anything wrong here?

WHERE 
(
    (
    CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))
    < ($(ThisExecTime)))
    AND
    (
    CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))
     >= ($(LastExecTime))) 
)
;

Note: Just doing the followng works fine, it seems to have something to do with the second >= part.

WHERE 
(CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))
    < ($(ThisExecTime)))
;

Re: Incremental Load problem

rwunderlich — Tue, 14 Aug 2012 21:05:12 GMT

What's the format of LastExecTime?

-Rob

Re: Incremental Load problem

Wed, 15 Aug 2012 10:05:03 GMT

Let ThisExecTime= num(now());

Let LastExecTime = ThisExecTime;

After it runs and does not return the wanted records I just go in a lower the amount in the variable by a day.

Re: Incremental Load problem

Tue, 21 Aug 2012 14:49:40 GMT

Full Script that I have been trying to use. Ran yesterday morning and had it set everything/load everything to the QVD. Ran it again today and nothing new came through. And Yes There are new results that are excluded for some reason.

If i remove

AND

(

CAST((CAST(changedate AS decimal(14)) + (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))

>= ($(LastExecTime)))

)

From the where clause it pulls in the correct updated/new records. This is the same format as the first portion and I have no idea why this is not working.

Let ThisExecTime= num(now());
SELECT
company + '-' + cast(ordernum as varchar) as primarykeyorderrel, 
    *
FROM orderhed
WHERE 
(
    (
    CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))
    < ($(ThisExecTime)))
    AND
    (
    CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))
     >= ($(LastExecTime))) 
)
;
Concatenate LOAD 
* FROM [...\Test.qvd(qvd)] (qvd)
WHERE NOT EXISTS(primarykeyorderrel);
Inner Join SELECT company, ordernum FROM orderhed;
If ScriptErrorCount = 0 then
STORE orderhed into [...\Test.qvd(qvd)];
Let LastExecTime =  num(ThisExecTime);

Edit: Here is the log for the SQL Where Clause

8/21/2012 9:41:29 AM: 0023 WHERE

8/21/2012 9:41:29 AM: 0024 (

8/21/2012 9:41:29 AM: 0025 (

8/21/2012 9:41:29 AM: 0026 CAST((CAST(changedate AS decimal(14)) + (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))

8/21/2012 9:41:29 AM: 0027 < (41142.40380787))

8/21/2012 9:41:29 AM: 0028 AND

8/21/2012 9:41:29 AM: 0029 (

8/21/2012 9:41:29 AM: 0030 CAST((CAST(changedate AS decimal(14)) + (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))

8/21/2012 9:41:29 AM: 0031 >= (41141.445451389))

8/21/2012 9:41:29 AM: 0032 )

Re: Incremental Load problem

Wed, 29 Aug 2012 12:17:36 GMT

I might have found my issue, I however need to wait for sales to get in and update some orders before I can test it. I believe it was with the Cast function where I was using decimal(14). I needed to tell it to use precision 16, with 11 decimals, so I changed it to DECIMAL(16,11)

Edit: I Updated an order to see if it works, and of course it does not...Back to square one.