Data Consolidation Question

[Anonymous]

Hi Experts,

I have a consolidation qvw program that consolidate monthly customer sales data file into a customer sales qvd file. It works but there are occasion when customer resubmit data for a particular month. This causes double consolidation for that customer because of the resubmitted sales.

Can I have your advise on how I can enhance the qvw

     1) to detect that the customer data file is a resubmission based on 2 fields, CustomerCode and SalesMonthYear?

     2) If detected that the data file is a resubmission, delete the old data for this customer and month from the customer sales qvd file.

     3) Once this old customer data is removed and the qvd re-created, the new data file should be processed as normal.

You can assume each data file submission is for 1 customer and 1 month only. Also, this is a scheduled server side job and hence, there should not be any prompting or human intervention.

Really appreciate any advice out there.

rgds,

Nickson

[Anonymous]

Hi,

I am not sure if I understand correctly. So basically you have one folder where customers submit their monthly sales data and sometime they resubmit previous month sales data?

Below suggestion may help you,

Load all files available in the folder in one table (say tableCurrent) and in the final table (qvd) use exist function to check the month if they are available and if exist remove from final qvd and concatenate tableCurrent.

tableCurrent:

Load

     ....

From all files;

finalQVD:

Load

     ...

From finalQVD

Where Not Exist(finalQVD.Month, tableCurrent.Month);

Concatenate

Load

     ...

Resident tableCurrent;

Drop table tableCurrent;

[Anonymous]

Thx for the reply but it is not that simple.

The customer data files are xls files placed in a folder. The consolidation qvw program executes daily and combines the data into a single qvd data file and then move the processed files to an archive folder.

So I guess it should be easier to check in the QV if the sales data already exist in the qvd file rather than to check if the same previous sales file can be found in the arhive folder. But the bigger problem for me is how to remove the previous consolidated sales data in the qvd and then import in the new set of sales data?