Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
oscarmqz
Creator III
Creator 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
rwunderlich

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



oscarmqz
Creator III
Creator III
Author

I tried that already, same results

krishna_2644
Specialist III
Specialist III

post some dummy data.

oscarmqz
Creator III
Creator III
Author

Here are the dummy files, thx for the help

oscarmqz
Creator III
Creator III
Author

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

oscarmqz
Creator III
Creator III
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]);

oscarmqz
Creator III
Creator III
Author

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

rwunderlich

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