topic Incremental Load without primary key and modification_date column in QlikView

Incremental Load without primary key and modification_date column

Tue, 21 Jul 2009 23:07:14 GMT

I am facing an issue with performing concatenation in the end of the qvd refresh with a combination of columns. with primary key (with another table) it's working fine, but with combination keys it 's giving error.

Any comments

Thanks

Preena

Following is the script:

SET QVDFILE =qvdfile.qvd;
LET datatable='myDataTable';

IF FileSize('$(QVDFILE)') > 0 THEN
SET QVD_EXISTS=1;
ELSE
SET QVD_EXISTS=0;
END IF

IF $(QVD_EXISTS) THEN

LOAD max(date(process_time)) as maxdate from $(QVDFILE)(qvd);
Let testdate = chr(39) & fieldValue('maxdate', 1) & chr(39);

$(datatable):
Directory;
Select a.*,month(Baldt) MonthNum from view a(nolock)
where process_time >= $(testdate) order by Year,Quarter,MonthNum,BalDt;
ELSE // If Qvd does not exists or deleted, it will do full reload
$(datatable):
Directory;
Select a.*,datepart(dw,BalDt) as wkday from view a(nolock) order by Year,Quarter,MonthNum,BalDt;
END IF

IF $(QVD_EXISTS) THEN
Concatenate Load * from $(QVDFILE)(qvd) where not exists(Inventory_id);
Inner Join Select Inventory_id from view(nolock);
END IF

STORE $(datatable) INTO $(QVDFILE);
Drop table $(datatable);

TestTable:
Load * from $(QVDFILE)(qvd);

Incremental Load without primary key and modification_date column

rwunderlich — Wed, 22 Jul 2009 02:18:39 GMT

In your example I see only one key (Inventory_id) shown. What are the other fields in your key? If your key consists of multiple fields, you must aggregate them into a single compund field for the exists() to work. I typically use autonumberhash128() to create the keys.

-Rob

Incremental Load without primary key and modification_date column

Thu, 23 Jul 2009 17:47:20 GMT

hi rob

I tried using autonumberhash also but its giving error.'out of virtual memory' after reloading the report.

query structure is like as follows:

SET PKexp=autonumberhash128(analysis_date,item);

IF $(QVD_EXISTS) THEN
Concatenate Load * from $(QVDFILE)(qvd) where not exists($(PKexp));
Inner Join Select analysis_date,item from view(nolock);
END IF

Incremental Load without primary key and modification_date column

Thu, 23 Jul 2009 20:11:20 GMT

It's giving error on inner join statement . On running concatenate, it's not giving any error but also not concatenating the new records with the existing one of qvd.

Incremental Load without primary key and modification_date column

rwunderlich — Fri, 24 Jul 2009 02:23:53 GMT

You will have to create the PK field in your new rows so they can be compared in the exists. In the load:

$(PKexp) as PK

and then in the concatenate.

Concatenate Load * from $(QVDFILE)(qvd) where not exists(PK, $(PKexp));

I assume your inner join is for deletes. You'll have to create a composite key there as well. Because not all rows in your QVD wil have a PK field, you'll have to regenerate it and then do the inner join. Like this: (not suntax checked)

DROP FIELD PK;
RIGHT JOIN LOAD DISTINCT *, $(PKexp) as PK RESIDENT $(datatable);
Inner Join LOAD $(PKexp) as PK;
Select analysis_date,item from view(nolock);
DROP FIELD PK;

-Rob

Incremental Load without primary key and modification_date column

Tue, 04 Aug 2009 19:07:34 GMT

I ve implemented code above but it is taking 1 and half hour to load qvd file and using Buffer(Incremental) keyword it took 40 minutes is there any way to improve load process further?

Incremental Load without primary key and modification_date column

Thu, 08 Oct 2009 22:09:48 GMT

If Table size is too huge then it will run out of memory

can we use conditional delete in that case?

Incremental Load without primary key and modification_date column

Mon, 24 Oct 2011 09:48:18 GMT

Hey Rob,
i know this thread is pretty old. but I am a newbie and have arrived here after searching for the exact same problem.
could you attach an example of the way you are creating the Hash key of field values and then stroring it, so as to use it as a Primary key.

I tried but doesnt seem to work ..
it would be helpuful if you could provide an example.

Regards

Leo .

Incremental Load without primary key and modification_date column

rwunderlich — Fri, 28 Oct 2011 02:20:03 GMT

Hi Leo,

I don't have a computer available at the moment to provide a complete example, but the composite key is created using the autonumber autonumberhash128 function. Look them up in the help. If you are going to store the key with the data then use the hash128 function.

Rob

Re: Incremental Load without primary key and modification_date column

joydipp1988 — Tue, 23 May 2017 18:52:48 GMT

Hi Rob,

In above comment you suggest to create primary key by following code "$(PKexp) as PK". But I'm not getting how will you create that PKexp ?

My current situation:

I have data in excel format which update everyday. We have date field and we can use that as Modified Field, but we don't have any filed that can be used as Primary Key. Also not getting any unique combination to create any composite key using Autonumber function. In that situation how to create Primary Key explicitly? I'm stuck into it badly. Please help.

Thanks in advance,

Joy

Re: Incremental Load without primary key and modification_date column

rwunderlich — Tue, 23 May 2017 21:51:02 GMT

The $(PKexp) variable was created earlier in the thread with a SET statement.

You can use the Date field as your key, but in this case it will be a group key, not a primary key. So as long as you are ok with always replacing the entire set for a Date try this:

MyData:

LOAD

foo,

Date,

Date as Date2,

... FROM xyz.xls;

Concatenate (MyData)

LOAD * FROM MyData.qvd (qvd)

WHERE NOT EXISTS (Date, Date2);

-Rob