Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
oscarmqz
Contributor III

Incremental load

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 incrementalAfter incremental
before.PNGafter.PNG
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Incremental load

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

View solution in original post

11 Replies
Highlighted
ziadm
Valued Contributor

Re: Incremental load

Hi

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

Highlighted
krishna_2644
Valued Contributor III

Re: Incremental load

try using:



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

i.e

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



Highlighted
oscarmqz
Contributor III

Re: Incremental load

I tried that already, same results

Highlighted
krishna_2644
Valued Contributor III

Re: Incremental load

post some dummy data.

Highlighted
oscarmqz
Contributor III

Re: Incremental load

Here are the dummy files, thx for the help

Highlighted
oscarmqz
Contributor III

Re: Incremental load

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

Highlighted
oscarmqz
Contributor III

Re: Incremental load

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

Highlighted
oscarmqz
Contributor III

Re: Incremental load

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

MVP & Luminary
MVP & Luminary

Re: Incremental load

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

View solution in original post