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: 
rustyfishbones
Master II
Master II

Incremental Load Issue WHERE EXISTS()

Hi All,

I have set up an incremental load

The initial extraction contains 270,000 rows, 17 days of data!.

I then take the max date from the extracted qvd and load today's data 11,340 rows

I then concatenate the two tables using WHERE NOT EXISTS(EventID)

The result I am expecting from the combined loads is 270,000 + about 20 rows as I can complete the process in under 1 minute

and the DB is generating about 20 rows per minute.

Instead of getting something like 270,020 I get 266,356.

In your experience could it be that the EventID is not unique???

Thanks

Alan

10 Replies
simondachstr
Luminary Alumni
Luminary Alumni

It's behaving as expected - here's  the reason and your fix  The exists issue

maxgro
MVP
MVP

maybe some duplicated EventID that you filter with not exists?

rustyfishbones
Master II
Master II
Author

Thanks Martin,

So if I can create a unique EventID it will solve my issue?

Regards

Alan

jyothish8807
Master II
Master II

Hi Alan,

Create a new field Rowno() in script.

Script:

Load

a,

b,

rowno() as Sino

from ,.;

Use Rown() as primary key.

eg.

where not exist (Sino)

Hope it works.

Regards

KC

Best Regards,
KC
rustyfishbones
Master II
Master II
Author

I don't think RowNo() will work.

If I load the history and add RowNo() it will start at 1

and

If I load everything for today and add the RowNo, it will start at 1

if I then do a WHERE NOT EXISTS, it will not include today as the history already contains 1

simondachstr
Luminary Alumni
Luminary Alumni

That's not the issue I think.

When loading the incremental part, add in an additional field EventID AS EventID_NEW

Then, when loading the second QVD file, use where not exists(EventID_NEW, EventID)

Before your store your QVD, make sure to Drop EventID_NEW as it's no longer needed.

jyothish8807
Master II
Master II

Hi Alan,

add rowno() in your main table and create a new QVD.

Use that QVD for incremental load.

Then Rowno() will work i guess.

Regards

KC

Best Regards,
KC
rustyfishbones
Master II
Master II
Author

Thanks Martin I tried that

But I got more records, 279000

Sent from my iPhone

simondachstr
Luminary Alumni
Luminary Alumni

Ugh, then that might be connected with the EventID I'm afraid. Consider maybe using AutoNumber() functionality instead to create a number out of a unique key (possibly eventid & Date)