Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rustyfishbones
Honored Contributor 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
Luminary
Luminary

Re: Incremental Load Issue WHERE EXISTS()

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

MVP
MVP

Re: Incremental Load Issue WHERE EXISTS()

maybe some duplicated EventID that you filter with not exists?

rustyfishbones
Honored Contributor II

Re: Incremental Load Issue WHERE EXISTS()

Thanks Martin,

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

Regards

Alan

jyothish8807
Honored Contributor II

Re: Incremental Load Issue WHERE EXISTS()

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
Honored Contributor II

Re: Incremental Load Issue WHERE EXISTS()

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

Luminary
Luminary

Re: Incremental Load Issue WHERE EXISTS()

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
Honored Contributor II

Re: Incremental Load Issue WHERE EXISTS()

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
Honored Contributor II

Re: Incremental Load Issue WHERE EXISTS()

Thanks Martin I tried that

But I got more records, 279000

Sent from my iPhone

Luminary
Luminary

Re: Incremental Load Issue WHERE EXISTS()

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)