Incremental Load without primary key and modification_date column

[#1]

Hi

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

[rwunderlich]

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

[#3]

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

[#4]

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.

[rwunderlich]

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

[#6]

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?

[#7]

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

can we use conditional delete in that case?

[#8]

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 .

[rwunderlich]

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

[joydipp1988]

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