Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I created an incremental load script however it is not adding the new (incremental) data to my QVD file. I have first connected to DB and copy the full data to QVD file. Subsequently I load from the QVD file and only select the new data from DB to be added to the QVD file.
Load Script
// Loading data from QVD
SRO:
LOAD
[Form Ref No],
[Form Name],
CompletionDate
FROM [lib://GS_Folder/SRO.qvd] (qvd);
// Find last modified date
Last_Updated_Date:
load max (CompletionDate) as Maxdate
resident SRO;
// Store last modified date to a variable
Let Last_Updated_Date=peek('Maxdate',0,'Last_Updated_Date');
// Delete Table SRO
Drop table SRO;
// Load new and incremental data from DB
LOAD
[Form Ref No],
[Form Name],
CompletionDate;
[Incremental]:
SELECT
"Form Ref No",
"Form Name",
CompletionDate
FROM DEA.COC.vwSROHeader where CompletionDate > $(Last_Updated_Date);
// Concatenate with QVD
Concatenate
LOAD
[Form Ref No],
[Form Name],
CompletionDate
FROM [lib://GS_Folder/SRO.qvd] (qvd);
// Replace old QVD file
Store Incremental into [lib://GS_Folder/SRO.qvd] (qvd);
// Drop Incremental Table
Drop table Incremental;
Load Status
Started loading data
SRO << SRO
(QVD (row-based) optimized)
Lines fetched: 1,154
Last_Updated_Date << SRO
Lines fetched: 1
Incremental << vwSROHeader
Lines fetched: 0
Incremental << SRO
(QVD (row-based) optimized)
Lines fetched: 1,154
Creating search index
Search index creation completed successfully
App saved
Finished successfully
0 forced error(s)
0 synthetic key(s)
Below are the 2 new records I want to add to QVD from DB
Form Ref No | Form Name | CompletionDate |
OSFT1911-00017 | COC - Form | 21/11/2019 5:06:29 PM |
CHQR1911-00033 | COC - Form2 | 21/11/2019 5:17:39 PM |
Any help is most appreciated. Thank you!
I think the error is due to CompletionDate which is in the format of 21/11/2019 5:06:29 PM.
Tried adding "LOAD TimeStamp(TimeStamp#(CompletionDate,'DD/MM/YYYY hh:mm:ss')) as CompletionDate," but it gave an error. Any idea?
Best I have is Help link for you, there is mention in there of needing to ensure you have the correct timestamp format per the backend DB, that may be where things are going wrong?
Hopefully reviewing that may help you find the problem, if you get further info, shout back. My post will also kick things back up in the list, so someone else may see things and be able to add something else too.
Regards,
Brett