topic Re: Incremental load in QlikView

Incremental load

Anonymous — Wed, 26 Aug 2015 17:29:05 GMT

Hi guys, this is breaking my head, I'm creating an incremental load, but I don't know what's happening, once I concatenate the old data using not exists, the old data is reduced to 1 line per field used in not exist, any clue?

Here's the script

Incremental:

LOAD *

FROM

[$(FN)](ooxml, embedded labels, table is [$(LastWk)]);

Concatenate

Load * FROM

[$(QVD_Path)\JRZ_RTL_CPC.qvd]

(qvd) where not Exists([PRD Week]);

Before incremental	After incremental

Re: Incremental load

ziadm — Wed, 26 Aug 2015 17:45:34 GMT

It looks that the two tables have been joined and this is because the two tables have the same structures. Therefore make a change in one of the tables by adding a new field

load

RecID,

left(RecID,6) as NRecID,

ProdWeek

from

concatenate load

RowID,

ProdWeek

from

where not exists(RecID,NRecID)

Hope this works

Re: Incremental load

Qrishna — Wed, 26 Aug 2015 17:46:04 GMT

try using:

where not Exists(previous table field,current table field)

i.e

where not Exists([PRD Week],[PRD Week])

Re: Incremental load

Anonymous — Wed, 26 Aug 2015 17:47:07 GMT

I tried that already, same results

Re: Incremental load

Qrishna — Wed, 26 Aug 2015 17:47:49 GMT

post some dummy data.

Re: Incremental load

Anonymous — Wed, 26 Aug 2015 17:56:42 GMT

Here are the dummy files, thx for the help

Re: Incremental load

Anonymous — Wed, 26 Aug 2015 17:58:26 GMT

I added a dummy field in the first table, same result

Re: Incremental load

Anonymous — Wed, 26 Aug 2015 18:08:37 GMT

I fixed it... turns out that "not exist" doesn't like integers... I guess this is a bug, here's the code that made it work

Incremental:

LOAD text([PRD Week]) as [PRD Week],

[FGI Week],

CYCLE,

BOL FROM

[LastFile.xlsx]

(ooxml, embedded labels, table is WK33);

Concatenate

LOAD text([PRD Week])as [PRD Week],

[FGI Week],

CYCLE,

BOL

FROM

[JRZ_RTL_CPC.qvd]

(qvd)

where not Exists([PRD Week]);

Re: Incremental load

Anonymous — Wed, 26 Aug 2015 18:37:56 GMT

The solution above was partial it only works the first time I ran it

Re: Incremental load

rwunderlich — Wed, 26 Aug 2015 20:09:27 GMT

where not Exists([PRD Week]) is behaving correctly. If you pass through a file, it's only going to keep the first occurrence of each [PRD Week] value. After that, the value "exists".

I take it what you want to do is use the set of values for any given week from the excel file, replacing what is in the QVD. Try this:

LOAD *,

[PRD Week] as [PRD Week Update]

FROM

[$(FN)](ooxml, embedded labels, table is [$(LastWk)]);

Concatenate

Load * FROM

[$(QVD_Path)\JRZ_RTL_CPC.qvd] (qvd)

where not Exists([PRD Week Update], [PRD Week]);

DROP FIELD [PRD Week Update];

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Incremental load

Anonymous — Wed, 26 Aug 2015 20:36:47 GMT

Thanks a lor Rob! This worked

I didn't know that's how not exists behaves, I really appreciate your explanation.

Regards,

Oscar Marquez

Re: Incremental load

harsh44_bhatia — Thu, 27 Aug 2015 15:49:29 GMT

Something more on the exists function on the same lines:

If you want to do the distinct load on a field we can use:

Load A, B, C, from Employees.csv where not exists (A);

This is equivalent to performing a distinct load on field A.

Read about this in the help file for exists function it will help you in clearing your concepts about exists function, also do understand on how Qlik stores data internally in form of bit stuffed pointers.