History of data in a qvd

[#1]

Hello Friends,

As I am trying to create historical data

from the database, I am copying the data into a qvd on a daily basis, the problem is what I am saving the data for yesterday which is altered for instance, If I have 1020 invoices yesterday but when I check today it will be very less like 998.

I would like to creata a qvd, it should store daily transactions the next day data should append with the previous day data

* Note:  Date1the field, which is not in the database.

CurrentData:

SQL

SELECT

    CURDATE() as Date1,

    Customer_id,

    Customer_Sub,

    Customer_Location,

    Invoice_No,

    Invoice_Amount,

    Count(Invoice_No) as NumberofInvoices,

    Sum(Invoice_Amount) as TotalAmt

FROM Master.dbo Group by Customer_id,Customer_Sub,Customer_Location, Invoice_No,Invoice_Amount;

STORE CurrentData into CurrentData.qvd(qvd);

LET vDate = Date(Today()-1,'DD/MM/YYYY');

LET vDate1= Date(Today(),'DD/MM/YYYY');

IF vDate < vDate1 then

Load * ,' ' as junk resident CurrentData;

     Concatenate(CurrentData)

     LOAD * from HistoricalData.qvd(qvd);

     Store CurrentData into HistoricalData.Qvd;

ENDIF;

DROP Table CurrentData;

Regards

Chriss

[Gysbert_Wassenaar]

LET vDate = Date(Today()-1,'DD/MM/YYYY');

LET vDate1= Date(Today(),'DD/MM/YYYY');

IF vDate < vDate1 then

This is always true so that check is unnecessary.

Have a look at the links in this discussion about incremental loading since it sounds like that's what you want to do.

[#3]

Hi Gysbert,

Yes, you are correct, it will be always true,Thanks for your valueable reply

I will test the data and update you.

Regards

Chriss