topic Re: Incremental Load Issue WHERE EXISTS() in QlikView

Incremental Load Issue WHERE EXISTS()

rustyfishbones — Fri, 17 Oct 2014 15:39:21 GMT

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

Re: Incremental Load Issue WHERE EXISTS()

simondachstr — Fri, 17 Oct 2014 15:57:23 GMT

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

Re: Incremental Load Issue WHERE EXISTS()

maxgro — Fri, 17 Oct 2014 15:58:16 GMT

maybe some duplicated EventID that you filter with not exists?

Re: Incremental Load Issue WHERE EXISTS()

rustyfishbones — Fri, 17 Oct 2014 16:11:16 GMT

Thanks Martin,

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

Regards

Alan

Re: Incremental Load Issue WHERE EXISTS()

jyothish8807 — Fri, 17 Oct 2014 16:17:46 GMT

Hi Alan,

Create a new field Rowno() in script.

Script:

Load

rowno() as Sino

from ,.;

Use Rown() as primary key.

eg.

where not exist (Sino)

Hope it works.

Regards

Re: Incremental Load Issue WHERE EXISTS()

rustyfishbones — Fri, 17 Oct 2014 16:24:58 GMT

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

Re: Incremental Load Issue WHERE EXISTS()

simondachstr — Fri, 17 Oct 2014 16:36:20 GMT

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.

Re: Incremental Load Issue WHERE EXISTS()

jyothish8807 — Fri, 17 Oct 2014 16:44:52 GMT

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

Re: Incremental Load Issue WHERE EXISTS()

rustyfishbones — Fri, 17 Oct 2014 17:08:18 GMT

Thanks Martin I tried that

But I got more records, 279000

Sent from my iPhone

Re: Incremental Load Issue WHERE EXISTS()

simondachstr — Fri, 17 Oct 2014 17:11:59 GMT

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)

Re: Incremental Load Issue WHERE EXISTS()

rustyfishbones — Fri, 17 Oct 2014 17:19:06 GMT

Thanks I will try that

Sent from my iPhone