Data Mismmatch

[smilingjohn]

Hi All, 

I have imp lemented incremental load . if it load once it gives the correct values  , but if i rleoad it again the data only for last date keeps doubling .  Why cant i ru the script twice or thirce to get the accurate data ?

Today 2020-May-08 the data in the DB was 100  and when i load the application it gets 200 if i reload it again the data will increase to 300 . why so  (for previous load date the data is correct ). I am using below script 

Sales:
LOAD
'LOAD_DATE',
'Sales'
FROM
[..\QVD\Sales.qvd]
(qvd);

Last_Updated_Date:
LOAD
Date(max(LOAD_DATE),'MM/DD/YYYY') as maxdate
Resident Sales;
LET vLast_Updated_Date = Peek('maxdate',0,'Last_Updated_Date');
DROP Table Last_Updated_Date;

Concatenate (Sales)
Sales:
Load
'LOAD_DATE',
'Sales'
SQL SELECT *
FROM "Sales_IT".Sales;
where "LOAD_DATE" > TO_DATE('$(vLast_Updated_Date)', 'mm/dd/yyyy');
store * from Sales into $(vPath)\Sales.qvd;
DROP Table Sales;

Is there a way where even if the application runs multiple time if should no keep increasing the records and remain the same as that of DB records . 

[rubenmarin]

So that means that manually stting the date doesn't works?

And this works?

SQL SELECT *
FROM "Sales_IT".Sales;
where "LOAD_DATE" > TO_DATE('02/10/2020)', 'mm/dd/yyyy')

 How was the query that worked?

[smilingjohn]

Hi @rubenmarin 

This gave an erorr while i was reloading the app 

[smilingjohn]

@rubenmarin 

This works 

where "LOAD_DATE" > to_date('05/10/2020', 'mm/dd/yyyy')

after loading this i got exactly 100 records 

[rubenmarin]

OK, but if you execute it for a second time it returns again the same 100 rows? Execute the full script code, with a date set manually, to check if it really works as expected: doing an incremental load and not duplicating rows.

[smilingjohn]

If i use this for second time it gives the double records as 200 . 

It is also behaving in the same as my previous script on max date the resords are loaded twice 

Is it really a date function or should somewhere i add not exost function to avoid the duplicate records abd let the app reload for multiple times .

[rubenmarin]

Sorry, I missed a step: load data from Sales qvd and create a textbox with =Date(max(LOAD_DATE),'MM/DD/YYYY')

Set this date as the new manual date on 

where "LOAD_DATE" > to_date('05/10/2020', 'mm/dd/yyyy')

Instead of 05/10/2020 set the new date, the date its supposed to be higher that 05/10/2020.

This is to confirm that manually doing the process it works, to try to catch where the logic fails.

[smilingjohn]

@rubenmarin  Thanks for your time . 

I have resolved it the reason behind it was that we need to build a primary key from the table and consider it as not exist whtin the QVD table while concatenating . 

This has resolved the issue . 

[rubenmarin]

Ok, but note that if the date filter doesn't works as expected, every time it loads all data from database. The idea of an incremental is to prevent that and query only the needed data from database.

[smilingjohn]

You are right @rubenmarin  , I  checked it intially the data loading was taking 2 mints and after this logic it is taking only 3 seconds and checking for the data greater than max date . 

And what what you mentioned  is the same problem I am facing while fetching the data from MySQL DB where upon the date filter is not working ,,I am tired on that which is in my other post . 

I will really greatfull to you if you can shower your knowledge on it . 

Thanks in Advance 

[rubenmarin · Accepted solution]

Working with dates can be very tricky because there are many different formats and configurations, that's why I asked to do manually. 

1- Test querys in database, checking that it really filters dates and the expectd format of the dates in query

2- replicate the procedute with qlikview script, changing dates manually until it works as expected.

3- Once it works you can do it with automated variables.

Also note that if you execute the query more that once by day you will need a timestamp to retrieve new records of the same day.

And lastly it will be even better if you save the time of execution at first (i.e. using a variable thisExecutionTime), load records before that execution time, and at the end of script raplce variable of lastExecutionTime with thisExecutionTime, this way you avoid some missing records that can have the same dates.

In Qlik help there is a schema on how an incremental reload can be done:

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

Some other post to guide:

https://community.qlik.com/t5/QlikView-Documents/Incremental-Load/ta-p/1478144

https://community.qlik.com/t5/QlikView-Documents/Incremental-load-for-beginner-with-example/ta-p/148...