Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
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
Highlighted
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
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
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
Contributor III

Re: Incremental load

I tried that already, same results

Highlighted
Valued Contributor III

Re: Incremental load

post some dummy data.

Highlighted
Contributor III

Re: Incremental load

Here are the dummy files, thx for the help

Highlighted
Contributor III

Re: Incremental load

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

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

Contributor III

Re: Incremental load

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

Highlighted
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