Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Load Incremental data to QVD, but how come it causes past data deleted?

Dear all,

The followings are what I am trying to do.

(1) Test_Data_qvf:Load "Position_Historical" data as of 2017/5/2 from "Test_DB.accdb" into "Position_Historical_QVD".

(2) Test_Data_qvf:Load Incremental "Position_Historical" data as of 2017/5/3 from "Test_DB.accdb" into "Position_Historical_QVD".

(3) Test_Structure_qvf:Load "Position_Historical" data from "Position_Historical_QVD".

If I run (1) and (3), the data can be loaded perfectly.

However, if I run (1), (2) and (3), the data as of 2017/5/3 cannot be loaded, and data as of 2017/5/2 is also deleted.

Could anyone advise what it's wrong in the following script?

===== [Script in Test_Data_qvf] =====

LIB CONNECT TO 'Test_DB';


//(1)Load Data as of 2017/5/2 to QVD
LET DataDate = Date(Date#('2017/5/2','YYYY/MM/DD'),'MM/DD/YYYY');

LOAD `Client_ID`,
    `Loan_USD`,
    `Market_Value_USD`,
    `Report_Date`,
    `Security_ID_BLG`;
SQL SELECT `Client_ID`,
    `Loan_USD`,
    `Market_Value_USD`,
    `Report_Date`,
    `Security_ID_BLG`
FROM `Position_Historical`
WHERE Report_Date=#$(DataDate)#;
STORE Position_Historical INTO [lib://Test_QVD/Position_Historical.QVD](QVD);


//(2)Load Incremental Data as of 2017/5/3 to QVD
LET DataDate = Date(Date#('2017/5/3','YYYY/MM/DD'),'MM/DD/YYYY');

QV_Table:
    LOAD `Client_ID`,
        `Loan_USD`,
        `Market_Value_USD`,
        `Report_Date`,
        `Security_ID_BLG`;
    SQL SELECT `Client_ID`,
        `Loan_USD`,
        `Market_Value_USD`,
        `Report_Date`,
        `Security_ID_BLG`
    FROM `Position_Historical`
    WHERE Report_Date=#$(DataDate)#;

    NoConcatenate LOAD
        `Client_ID`,
        `Loan_USD`,
        `Market_Value_USD`,
        `Report_Date`,
        `Security_ID_BLG`
    FROM [lib://Test_QVD/Position_Historical.QVD](QVD)
    WHERE NOT EXISTS(Report_Date);

STORE QV_Table INTO [lib://Test_QVD/Position_Historical.QVD](QVD);
DROP TABLE QV_Table;

===========================================================

6 Replies
sanjay006
Creator
Creator

Hi Rex !

I have updated your script plz check

===== [Script in Test_Data_qvf] =====

LIB CONNECT TO 'Test_DB';

Record:

LOAD `Client_ID`,

    `Loan_USD`,

    `Market_Value_USD`,

    `Report_Date`,

    `Security_ID_BLG`;

SQL SELECT `Client_ID`,

    `Loan_USD`,

    `Market_Value_USD`,

    `Report_Date`,

    `Security_ID_BLG`

FROM `Position_Historical`;

STORE Position_Historical INTO [lib://Test_QVD/Position_Historical.QVD](QVD);

update_Record:

LOAD*

Resident Record

Order by Report_Date;

LET vUpdated_DateHeader= Peek('Report_Date',-1,'update_Record');

DROP Table Record;

LIB CONNECT TO 'Test_DB';

test:

LOAD `Client_ID`,

    `Loan_USD`,

    `Market_Value_USD`,

    `Report_Date`,

    `Security_ID_BLG`;

    Where Report_Date>='$(vUpdated_DateHeader)';

   

SQL SELECT `Client_ID`,

    `Loan_USD`,

    `Market_Value_USD`,

    `Report_Date`,

    `Security_ID_BLG`

   FROM `Position_Historical`;

Concatenate

LOAD `Client_ID`,

    `Loan_USD`,

    `Market_Value_USD`,

    `Report_Date`,

    `Security_ID_BLG`;

FROM [lib://Test_QVD/Position_Historical.QVD]

(qvd)

Where not Exists (Client_ID);

sanjay006
Creator
Creator

If you want to change the dates you can easily pass the dates in variable and use.

sanjay006
Creator
Creator

LET vUpdated_DateHeader= Peek('Date(Date#('2017/5/3','YYYY/MM/DD'),'MM/DD/YYYY');',-1,'update_Record');


but plz confirm that the format of the variable should be same as the Report Date field.

Anonymous
Not applicable
Author

Dear Sanjay,

May I have few more questions:

(1) Shouldn't I add the line below at the bottom of script you modified since the incremental data was not added in QVD?

    STORE test INTO [lib://Test_QVD/Position_Historical.QVD](QVD);

(2) If I add the line in script, QVD would only have data as of 'vUpdated_DateHeader', and data before 'vUpdated_DateHeader' seems be deleted?

===== [script you modified] =====

test:

LOAD `Client_ID`,

    `Loan_USD`,

    `Market_Value_USD`,

    `Report_Date`,

    `Security_ID_BLG`;

    Where Report_Date>='$(vUpdated_DateHeader)';

  

SQL SELECT `Client_ID`,

    `Loan_USD`,

    `Market_Value_USD`,

    `Report_Date`,

    `Security_ID_BLG`

   FROM `Position_Historical`;

Concatenate

LOAD `Client_ID`,

    `Loan_USD`,

    `Market_Value_USD`,

    `Report_Date`,

    `Security_ID_BLG`;

FROM [lib://Test_QVD/Position_Historical.QVD]

(qvd)

Where not Exists (Client_ID);

==========================================

sanjay006
Creator
Creator

Ans 1 : i forgot to mention the below line you can add no issue with tha.

Ans 2: No it will not be deleted it just got concat with the previously data available.

Anonymous
Not applicable
Author

Dear Sanjay,

I will check the script again since the past data always be deleted on my side when I run incremental add.