how to create incremental load without primary key

[jayati_shrivast]

Hi All,

I have a qvd table with field columns as NAME, DATE and DURATION. I need to create its incremental load. How can I create the same as I dont have field named as ID in the qvd which can be considered as primary key.

please help me as to how it can be implemented?

 Thanks

[pradosh_thakur]

Can you create a field called updated_time or updated_date which updates everytime the rows are updated?
You can then use a where clasue with updated_date >= last reload date

[jayati_shrivast]

can you explain me with some example?

[pradosh_thakur]

Check out below.

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

[HirisH_V7]

Hi 

Check this code,

Max:
LOAD Max(DATE) as Max_Date
FROM Incremental.xlsx (ooxml, embedded labels, table is Sheet1);

let VMaxDate= Peek('Max_Date');

Drop table Max;


Incremental:
LOAD NAME, DATE, DURATION,RecNo() as ID
FROM Incremental.xlsx (ooxml, embedded labels, table is Sheet1) 
where DATE>=Date($(VMaxDate));


Concatenate 

LOAD NAME, DATE, DURATION ,ID
FROM P_Data.qvd (qvd) where not Exists(ID);


Store Incremental into P_Data.qvd;

Drop table Incremental;


Data:
LOAD NAME, DATE, DURATION ,ID
FROM P_Data.qvd (qvd);

 I Have used Recno() for creating a Unique ID. Pl Check if this is helpful in your scenario or not. I haven't checked for duplicates though, have a check if require we can do inner join and remove those.

PFA Excel example and QVW For Ref.

HTH,

Hirish 

[jayati_shrivast]

thankyou so much. can u tell if i want to apply inner join in this, how will i implement that?

[HirisH_V7]

As it will be based on repetition of rows, in current scenario as we took Recno() as an unique key. Here we will not end up with duplicates .

If Any case duplicate or any unwanted exists with a unique key means, An inner join of the updated and previous data set will result in inter-sectional data set of both i.e. common in both.

PF Below Updated code:

Max:
LOAD Max(DATE) as Max_Date
FROM Incremental.xlsx (ooxml, embedded labels, table is Sheet1);

let VMaxDate= Peek('Max_Date');

Drop table Max;


Incremental:
LOAD NAME, DATE, DURATION, NAME& DATE& DURATION  as ID
FROM Incremental.xlsx (ooxml, embedded labels, table is Sheet1) 
where DATE>=Date($(VMaxDate));

Concatenate  //Appending only new records

LOAD NAME, DATE, DURATION ,ID
FROM P_Data.qvd (qvd) where not Exists(ID);

inner join     //For Removing deleted rows or duplicates in master table. 

LOAD NAME& DATE& DURATION  as ID
FROM Incremental.xlsx (ooxml, embedded labels, table is Sheet1) ;


Store Incremental into P_Data.qvd;
//EXIT SCRIPT;
Drop table Incremental;


Data:
LOAD NAME, DATE, DURATION ,ID
FROM P_Data.qvd (qvd);

PFA QVW and excel for ref.

HTH,

Hirish

[jayati_shrivast]

I tried this in my script but I am getting an error as ID not found.
I applied the REC function in the source table which is coming from sql database.
How can I resolve this?

[HirisH_V7]

In my last updated code i haven't used recno() fucntion. I created a unique key there, Because it will address Deletion of unwanted records, as you have asked in previous post.

Could you please send the snippet of code.

Make sure you should create QVD First and store it. So on top that this code should run incrementally. As i am not aware how your data set responds for code, cant comment on that.

HTH,
Hirish