Insert and update (no delete) Load QVD

[MarioCenteno]

I'm trying to modify my script to load data by means of a QVD, but I have the following problem.

I need to update the records in the QVD those registries that have had changes in the database, now my script only inserts data, can someone help me?

LET vQVDexists = NOT ISNULL(QVDCreateTime('lib://QVDS/VENTA_GC_2018.qvd')); 


LET vNow = Now();  
IF $(vQVDexists) THEN;  
TRACE('QVD Exists, we will append data');  
    LatestUpdate:    
    LOAD  Date(Max(date_process) as MaxDate  
FROM [lib://QVDS/VENTA_GC_2018.qvd]  
    (qvd)  
    ;  

    Trace('Load LatestUpdate');  

    LET vMaxDate = Peek('MaxDate',0,'LatestUpdate');  
    TRACE('Max update' & '$(vMaxDate)');  

   NewData:  
 SELECT * from table
where date_process > '$(vMaxDate)'      




Trace('Concatenating the new data with the old data');  
    Concatenate(NewData)  
    LOAD * FROM [lib://QVDS/VENTA_GC_2018.qvd]  
    (qvd)  
    ;  
    STORE NewData into [lib://QVDS/VENTA_GC_2018.qvd] (qvd);  
    ;  
  ELSE  
  Trace('QVD no exise, cargaremos toda la data');  

  ///FULL LOAD




let vStartDate = Date(MonthStart(MonthStart(Today())-1),'YYYYMMDD');
//vDate = Date(Date#('20180901','YYYYMMDD'),'YYYYMMDD');  
vDate = Date(Date#(vStartDate,'YYYYMMDD'),'YYYYMMDD');  


DO WHILE vDate <= Today(1)   


[VENTA_GC_2018]:
 SELECT * FROM table
WHERE date_process = TO_DATE('$(vDate)','YYYYMMDD')    




LET vDate = Date(Date#('$(vDate)', 'YYYYMMDD')+1 ,'YYYYMMDD');  





LOOP


STORE VENTA_GC_2018 into [lib://QVDS/VENTA_GC_2018.qvd];



 //COMBINE NEW DATA WITH EXISTING QVD                   


CONCATENATE (VENTA_GC_2018)


           LOAD *


           FROM [lib://QVDS/VENTA_GC_2018.QVD] (QVD)

            //Drop Table VENTA_GC_2018
;

end if


;

[swuehl]

Have a look at

https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/LoadData/use-QVD-fi...

[MarioCenteno]

Thanks Stefan, I told you that I have read the article but it still does not work

[swuehl]

Sorry, can't see that you are using something like

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT Exists(PrimaryKey);

when concatenating the historic data from the QVD.

[MarioCenteno]

I already made the modification, the problem I have is in this condition date_process> '$ (vMaxDate)', there are some data that for some reason were not loaded in the QVD, I have a record that its date_process = 10/10/2018 but as now I am loading data> 10/15/2018 that record can not load it.

I hope to have explained

[MarioCenteno]

petter-s‌ Could you help me?

[petter]

To me it seems that the date_process is really not the date that the row was updated or modified - but probably the date that something was processed but not the row information as such. So you don't actually have the right column. If you have another column in your SQL database that reflects the real modified/updated date you should use that.

If not - you would have to rely on another technique to identify a modified/changed/updated row. You can create a hash of the entire source row of the SQL table in question and store that as a indicator of change instead of a date. It is much less efficient but it works well.

Hash256()-function is the function that can help you generate the 43 character string that pose as a fingerprint and identifier of any row. If the hash-value is equal the row is equal. So only load rows that hasn't been loaded before based on whether the hash-value exists or not. So instead of retrieving the Max() date you will have to have the hash-values of all rows in memory and use Exists()-function in your WHERE clause of a LOAD statement that precedes your SQL statement. The SQL statement has to load all rows from the source table unfortunately - so it will be much less efficient.

Ideally the hash would be created in the source database and the logic implemented there. If something prevents you from doing it at the source database level you will have to implement it in the Qlik load script with the Hash256()-function mentioned.

BTW - your script has syntax errors and logical errors - so I don't see how this script can be run at all as it stands even though the main logic seems to be correct...

[swuehl]

Mario Centeno wrote:

I already made the modification, the problem I have is in this condition date_process> '$ (vMaxDate)', there are some data that for some reason were not loaded in the QVD, I have a record that its date_process = 10/10/2018 but as now I am loading data> 10/15/2018 that record can not load it.

I hope to have explained

Well, you haven't explained much so far.

For example:

- What is the purpose of your code starting from line 40?

- What is the structure of your 'table' SQL table? What is the primary key and what is the field indicating a modification time? You need both to use an incremental update load (check the linked help page for more information).

- Have you checked the SQL code needed to filter your date_process field? Compare your line 20 with line 50 and maybe double check with your local SQL developers how to write correct WHERE clause (correct use of the vMaxDate variable expansion).