topic Partial reload for QVW like incremental for QVD in QlikView

Partial reload for QVW like incremental for QVD

Thu, 24 May 2012 09:25:22 GMT

Hello all,

I created incremental load for QVD and worked... but how I do "incremental" - partial reload for QVW

How can do something like below example for a partial reload for QVW

This is incremental load for QVD (with insert and update)

TAB:

LOAD MaxString(VALID_BEGIN) AS MAXDATE

FROM C:\Users\Adi\Desktop\CLAIM.QVD (qvd);

LET currentmaxdate= DATE#(PEEK('MAXDATE',0,'TAB'),'DD/MM/YYYY');

SET PrimaryKey = CLAIM_ID& ' ' &ID& ' ' &VALID_BEGIN;

CLAIM:

LOAD $(PrimaryKey) AS PK , *

FROM

'C:\Users\Adi\Desktop\CLAIM.xlsx'

(ooxml, embedded labels, table is Sheet1)

where VALID_BEGIN >= '$(currentmaxdate)' ;

Concatenate LOAD * FROM 'C:\Users\Adi\Desktop\CLAIM.QVD'(QVD)

WHERE NOT Exists(PK, $(PrimaryKey)) ;

DROP FIELD PK;

STORE CLAIM INTO 'C:\Users\Adi\Desktop\CLAIM.QVD'

Any idea?!

Partial reload for QVW like incremental for QVD

jvitantonio — Thu, 24 May 2012 10:07:55 GMT

You can use the Exist functions

If you have a tableA with a key KeyA then do:

LOAD * FROM yourQVD.qvd (qvd)

where not exists(KeyA);

Partial reload for QVW like incremental for QVD

Thu, 24 May 2012 12:09:43 GMT

And how can I use for UPDATE and INSERT into QVW?

Partial reload support only ADD or REPLACE.

I tested but don't work or I missed something... here it's the code

IF IsPartialReload() then

SET PrimaryKey = CLAIM_ID& ' ' &VALID_BEGIN;

CLAIM:

LOAD $(PrimaryKey) AS PK , *

FROM

C:\Users\Administrator\Desktop\CLAIM2.qvd //have 120 rows (update and insert - no delete)

(qvd)

WHERE NOT Exists(PK, $(PrimaryKey)) ;

DROP Field PK ;

else

CLAIM:

LOAD *

FROM

C:\Users\Administrator\Desktop\CLAIM.qvd //have 100 rows

(qvd);

end if;

Partial reload for QVW like incremental for QVD

jvitantonio — Thu, 24 May 2012 13:04:59 GMT

Sorry, but what's the problem with the code?

Partial reload for QVW like incremental for QVD

Thu, 24 May 2012 14:58:55 GMT

I think I solved...

But I see is running much harder with partial reload than full reload.... Is normal? because for me is much important the speed

Partial reload for QVW like incremental for QVD

Thu, 24 May 2012 20:43:45 GMT

after I struggled for hours with it.... I don't used because running more than an full reload

Thanks qlikuser14 for help.

This is the right code for me for partial reload(update+insert):

SET PrimaryKey = CLAIM_ID& ' ' &VALID_BEGIN;

IF IsPartialReload() then

CLAIM:

REPLACE ONLY LOAD $(PrimaryKey) AS PK , *

FROM

C:\Users\Administrator\Desktop\CLAIM2.qvd

(qvd)

WHERE Exists(PK,$(PrimaryKey)) and VALID_UNTIL <> '31/12/9999 00:00:00';

CLAIM:

ADD ONLY LOAD $(PrimaryKey) AS PK , *

FROM

C:\Users\Administrator\Desktop\CLAIM2.qvd

(qvd)

WHERE NOT Exists(PK,$(PrimaryKey)) ;

else

CLAIM:

LOAD $(PrimaryKey) AS PK, *

FROM

C:\Users\Administrator\Desktop\CLAIM.qvd

(qvd) ;

end if;