Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Incremental Load problem

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

;

4 Replies

Re: Incremental Load problem

What's the format of LastExecTime?

-Rob

Not applicable

Re: Incremental Load problem

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.

Not applicable

Re: Incremental Load problem

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  )

Not applicable

Re: Incremental Load problem

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.

Community Browser