Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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!