Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
ziadm
Specialist
Specialist

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

krishna_2644
Specialist III
Specialist III

try using:



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

i.e

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



Anonymous
Not applicable
Author

I tried that already, same results

krishna_2644
Specialist III
Specialist III

post some dummy data.

Anonymous
Not applicable
Author

Here are the dummy files, thx for the help

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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