Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

Re: Data Mismmatch

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?

Highlighted
Creator III
Creator III

Re: Data Mismmatch

Hi @rubenmarin 

This gave an erorr while i was reloading the app 

smilingjohn_0-1589182428307.png

 

Highlighted
Creator III
Creator III

Re: Data Mismmatch

@rubenmarin 

This works 

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

 

after loading this i got exactly 100 records 

 

Highlighted

Re: Data Mismmatch

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.

 

Highlighted
Creator III
Creator III

Re: Data Mismmatch

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 .

Highlighted

Re: Data Mismmatch

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.

Highlighted
Creator III
Creator III

Re: Data Mismmatch

@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 . 

Highlighted

Re: Data Mismmatch

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.

 

Highlighted
Creator III
Creator III

Re: Data Mismmatch

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 

 

Highlighted

Re: Data Mismmatch

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...

 

View solution in original post