Incremental load from no. of Records in the Data

[raZor]

Hello All,

i have a scenario where i am pulling data from SQL and then extracting it in .qvd file.

In the SQL data there is only a date field with no date time field from which i can get to know when the data was recorded.

can anyone suggest how to insert date time field in .qvd file for every record so that i can do incremental load?

or any other method to do increment load, like no. of records etc.

Regards 

[martinpohl]

how can you insert an information (time) into your sql that are not exists?

Maybe you can use a record id which is growing with every new record so you can load max max recordno from qvd and use them as a filter in sql statement?

Regards

[raZor]

Thanks for the quick response, i dont have any such filed from which i can get to know that new records are loaded in SQL data, other than a unique invoice_number with each record. 

can you give me an example to create variable with the count of distinct invoice_number  and then to use it for incremental load in the qvd from SQL?

Regards

[martinpohl]

InvoiceNo:

load

Invoice 

from Invoice*.qvd (qvd);

MaxInvoice:

load

max(InvoiseNo) as MaxNo

resident InvoiceNo;

let vMaxNo = peek(ÄMaxNo,0,'MaxInvoice');

drop table InvoiceNo;

drop table MaxInvoice;

Data:

sql select * from Yourdatabase

where InvoiceNo > $(vMaxNo);

store Data into lib://Invoice_$(vMaxNo);

drop table Data;

So you store the datas with MaxInvoiceno in filename. Here you also can use date or timestamp instead.

Regards

[raZor]

Hi,

i tired this-

// *****TIME Variable Declaration*****//
LET ThisExecTime= Now();

// *****START CODE Variable Declaration*****//
Let vtoday = date(today());
// PAth for connexion
Let DB_connection = 'pcs';
//Let DB_connection2 = 'PCSOFT';

// ***** Repertory for the QVD files*****//
LET name_app = '$Sales_Data';
//name of the table
LET table_1 = 'A';
// ***** PATH of QVD files *****//
LET vSaveQVD = 'lib://test/';
LET qvd_1 = '$(vSaveQVD)/$(name_app)/$(table_1).qvd';

// **************************** Variable Declaration - END CODE *****************************//

$(name_app):
LIB CONNECT TO '$(DB_connection)';
SQL select

c.type_desc as territory,
t.doc_no as invoiceno,
t.doc_date as doc_date, t.party_no,
cast(pm.PARTY_NAME as varchar(60)) as party_name,
sum(i.IM_BASIC)/10000000 as Total_IN_CR

from trinvs t

left join imtrans i on t.doc_type = i.doc_type and t.doc_no = i.doc_no

where 

(MONTH(t.DOC_DATE) = MONTH(getdate()) AND YEAR(t.DOC_DATE) = YEAR(getdate()) )

group by t.party_no,t.doc_no

// ***** STORE QVD files *****//

store $(name_app) into '$(qvd_1)'(qvd);

InvoiceNo:

load

invoiceno

FROM [lib://$Sales_Data/A.qvd]
(qvd);

MaxInvoice:

load

max(invoiceno) as MaxNo

resident InvoiceNo;

let vMaxNo = peek(MaxNo,0,'MaxInvoice');

drop table InvoiceNo;

drop table MaxInvoice;

Data:

sql select * from trinvs

where doc_no > '$(vMaxNo)';

 store Data into lib://Invoice_$(vMaxNo);

drop table Data;

can you please check what is the issue? 

Regards

[raZor]

Hello, can you please check my code? Regards