Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rexliao1031
New Contributor II

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
Contributor

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

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
Contributor

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

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

sanjay006
Contributor

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

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.

rexliao1031
New Contributor II

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

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
Contributor

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

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.

rexliao1031
New Contributor II

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

Dear Sanjay,

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