Incremental Load - keep track of newly added records in extra QVD

[louwriet]

Hi All,

I have a scenario where I extract daily sales from 7 stores. There is no primary key in data and I use transaction date and store number instead for primarykey - there is no delete in records.(I have just to make sure that all transactions is loaded per day) Some days a store skip to send the data then the day after I must import that data of the skipped day also. There is no timestamp in data. This store data is already Imported into a QVD and this STOCK_TRANSACTION is my source.

My logic is = test if transactiondate and storenumber exists in my QVD if not add the transactions.

This approach works fine.

What I am getting stuck with is I also need to create a extra QVD with just the new records that was added. The purpose of this is to FTP this update QVD to remote server to import there. I have tried the Flagg approach to try and flag just newly added records everyday so that I can at the end of update create a QVD with the newly added records, but I don't get the desired result.

My idea was to flag newly added records with a '1'  else records will be flagged with '0' but every record has a '0' after run.

See my approach below. Help would be appreciated

IF NOT ISNULL(QvdCreateTime('$(vQVDPath_ExtractStep1)STOCK_TRANSACTION_STEP1.qvd')) THEN

  STOCK_TRANSACTION_STEP1 :
LOAD
     stock_date,
     stock_num,
     ......

   .........

     .......
     Branch_Code,
     LoadKey,   // Loadkey consist out of stock_date and Branch_Code
     LoadKey as LoadKeyUpdate,
     LoadDate, // this is a timestamp I create when data is loaded in QVD
     '0' as FlagUpdate
FROM
$(vQVDPath_ExtractStep1)STOCK_TRANSACTION.qvd  // SOURCE QVD
(qvd);

  Concatenate
    
LOAD
     stk_date,
     stk_num,
   .......

.....

........
     Branch_Code,
     LoadKey,
     LoadDate,
      '1' as FlagUpdate
FROM
$(vQVDPath_CKS_ExtractStep1)STOCK_TRANSACTION_STEP1.qvd
(qvd)
Where NOT Exists(LoadKeyUpdate,LoadKey);

DROP Field LoadKeyUpdate;

ELSE  // For first run if QVD don't exist

  STOCK_TRANSACTION_STEP1 :

LOAD
     stk_date,
     stk_num,
   ........

........

.........
     Branch_Code,
     LoadKey,
     LoadDate,
      '1' as FlagUpdate
FROM  $(vQVDPath_ExtractStep1)STOCK_TRANSACTION.qvd (qvd);

ENDIF

call create_qvd('STOCK_TRANSACTION_STEP1','STOCK_TRANSACTION_STEP1'); // STORE DATA TO QVD

// my idea was to add additional step in here to Extract records out of 'STOCK_TRANSACTION_STEP1' QVD that is flagged with '1' AND that would have been newly added records for the day and this QVD I would then FTP to remote server.

Hope somebody have suggestion.

Thank so much. Regards Louw

[effinty2112]

Hi Louwrie,

I hope I've understood you correctly.

In this part of your script:

Concatenate
LOAD
     stk_date,
     stk_num,
   .......

.....

........
     Branch_Code,
     LoadKey,
     LoadDate,
      '1' as FlagUpdate
FROM
$(vQVDPath_CKS_ExtractStep1)STOCK_TRANSACTION_STEP1.qvd
(qvd)
Where NOT Exists(LoadKeyUpdate,LoadKey);

Instead of immediately concatenating these new records why not make a temporary table instead? Store the temporary table to a qvd of new records, then concatenate the temporary table to your existing table, then delete the temporary table.

[louwriet]

Hi Andrew,

Thank you for the reply.

Your suggestion sounds like a good one. Just not sure how to implement this temp table, my second  script after the concatenate reference  LoadKeyUpdate  field that is in the load script before the concatenate - this is now in the Where NOT Exists. If I do not concatenate in first place how will I referernce the LoadKeyUpdate field to identify new records.

Maybe I am missing a point here.

Thank so much for help.

Regards

Louwrie

[effinty2112]

How about:

Temp:

LOAD
     stk_date,
     stk_num,
   .......

.....

........
     Branch_Code,
     LoadKey,
     LoadDate,
      '1' as FlagUpdate
FROM
$(vQVDPath_CKS_ExtractStep1)STOCK_TRANSACTION_STEP1.qvd
(qvd)
Where NOT Exists(LoadKeyUpdate,LoadKey);

STORE Temp into  ....; // whatever you call this qvd and wherever you store it

Concatenate (STOCK_TRANSACTION_STEP1)

Load * Resident Temp;

Drop table Temp;

// Carry on original script from here

DROP Field LoadKeyUpdate;

ELSE  // For first run if QVD don't exist

  STOCK_TRANSACTION_STEP1 :

...

Hope this helps!