topic Help please :) with incremental load in QlikView

Help please :) with incremental load

Wed, 08 Apr 2015 11:16:13 GMT

Hi Guys,

Im a bit stuck im trying to append a query in Postgres Sql into a qvd file, when I run the append query it is telling me there is an error in the where not exists statement highlighted red below. can anyone tell me how I am going wrong ?

[ORIGINAL QVD STATEMENT]

Concatenate

select q.* from

(

SELECT

o.orderid,

o.orderid||'-'||oa.orderatomid as primarykey,

o.status,

customer_name(c.custid) as custname,

c.finance_ref,

oa.orderatomid,

oa.state,

a.ldesc,

s.lname as supplier_name,

(

SELECT

CONCAT(oap.value)

FROM

orderatomparams oap

INNER JOIN atomparams ap ON (ap.atomparamid = oap.atomparamid)

WHERE

oap.orderatomid = oa.orderatomid

AND ap.sdesc = 'CBUK'

AND oap.value IS NOT NULL

AND oap.value != ''

) as cbuk_nos,

(

SELECT

CONCAT(oap.value)

FROM

orderatomparams oap

INNER JOIN atomparams ap ON (ap.atomparamid = oap.atomparamid)

WHERE

oap.orderatomid = oa.orderatomid

AND ap.sdesc = 'BBEU'

AND oap.value IS NOT NULL

AND oap.value != ''

) as bbeu_refs,

(

SELECT

CONCAT(oap.value)

FROM

orderatomparams oap

INNER JOIN atomparams ap ON (ap.atomparamid = oap.atomparamid)

WHERE

oap.orderatomid = oa.orderatomid

AND ap.sdesc = 'CIRC-REF'

AND oap.value IS NOT NULL

AND oap.value != ''

) as circrefs

FROM

orders o

INNER JOIN orderatoms oa ON (o.orderid = oa.orderid)

INNER JOIN atoms a ON (a.atomid = oa.atomid)

INNER JOIN customer c ON (COALESCE(o.billcustid, o.custid) = c.custid)

LEFT JOIN supplier s ON (s._id = oa.supplier_id)

WHERE

a.grpid IN ('DSL')

AND o.commissiondate IS NOT NULL

) q

where

(q.circrefs IS NOT NULL AND q.circrefs != '')

OR (q.cbuk_nos IS NOT NULL AND q.cbuk_nos != '')

OR (q.bbeu_refs IS NOT NULL AND q.bbeu_refs != '')

AND NOT EXISTS (primarykey)

order by

q.custname, q.orderid, q.orderatomid;

Re: Help please :) with incremental load

pokassov — Wed, 08 Apr 2015 11:22:56 GMT

Hi!

You need primarykey and datetime field

First of all you have to select .... where datetime>(your max extracted previous datatime).

Then you add data from previous stored qvd file and there use AND NOT EXISTS (primarykey).

Examply from help:

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT Exists(PrimaryKey);

STORE QV_Table INTO File.QVD;

You can get LastExecTime like this way:

t1:

load max(ModificationTime) as ModificationTime

from File.QVD (qvd);

let LastExecTime=peek('ModificationTime ',0,'t1');

drop table t1;

Sergey

Re: Help please :) with incremental load

Wed, 08 Apr 2015 12:00:23 GMT

Thanks Sergey, it seems to be not recognising my primary key do you know why it is doing it ? Is strange as I've done an incremental load before without dates and it seemed to work okay using this method

Re: Help please :) with incremental load

pokassov — Wed, 08 Apr 2015 12:12:37 GMT

Obviously, you are trying to mix some different approaches.

Qlik can give parameter to sql, i.e. LastExecTime, but it cannot give list of "primary keys".

sql also consists error. You can not use condition like you have writen. NOT EXISTS (primarykey).

Classical SQL is (not exists (select ... from ... where ...)

You have to change your aproach.

1. extract table by table. using primary key and time stump for each.

2. join and calculate data in qlikview using qlikview logic.