topic Re: Data Mismmatch in QlikView

Data Mismmatch

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

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 .

Re: Data Mismmatch

rubenmarin — Fri, 08 May 2020 11:17:19 GMT

Hi, I don't know if it's a typo posting the question but there is a semicolon at the end of the FROM clause, before the where clause, so maybe the query is being executed without the where clause.

If it's not because of this I would try to manually set the date, so intead of $(vLast_Updated_Date) put the date and check the query, once it works you can easily replace it in QV script.

Re: Data Mismmatch

smilingjohn — Fri, 08 May 2020 18:53:38 GMT

Thanks @rubenmarin for reply

Yes it is a typo error . If I manually give the date then how will this be automated ?

My script works good enough for daily once reload , the problem is when I reload it more than once .

Re: Data Mismmatch

smilingjohn — Mon, 11 May 2020 05:07:08 GMT

Hi Expertise any help on this ?

Re: Data Mismmatch

Durgadevikumar — Mon, 11 May 2020 06:11:57 GMT

Hi,

Try to avoid the semicolon in the from Clause before the where condition. use below script i made the small challenges in your code and its working perfectly for me.

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' > $(Last_Updated_Date);
store Sales into $(vPath)\Sales.qvd;

Re: Data Mismmatch

rubenmarin — Mon, 11 May 2020 06:35:56 GMT

Hi, the manual date is just to confirm that the where condition to filter by date works as expected. Once it works you should replace it by something automated but first check it setting a manual date.

If it works post the query using the manual date.

Re: Data Mismmatch

smilingjohn — Mon, 11 May 2020 06:43:12 GMT

@rubenmarin

My above query works fine , the only concern is I have to user where doesnot exists function to avoid the duplicate records on Max date . As i came to know that the incremental script i am using can be loaded only one time , if i try to load it more than one time then there will be duplcate records on max date seems .

I need your suggestion on this and how can i make the script reload to 3 times a day , as i need to do so because every 5 hours the Db will be loaded with changes .

Thanks @rubenmarin

Re: Data Mismmatch

rubenmarin — Mon, 11 May 2020 06:56:41 GMT

Have you really tried to set manual date in qliview script?

Can you post the same sentnces but with the manual used that really works?

Form what I see in your script if you load one time in a day the last date should be the current date, so the second execution won't retrieve any data because you are asking by "LOAD_DATE" > TO_DATE('$(vLast_Updated_Date)', 'mm/dd/yyyy').

And you said that not only it still loads data from the current day, it also loads all the data so it's like the date filter doesn't works at all

So because of that is for what I'm asking to test setting manual date in QV scrit, and if it really works post the script with the amanual date that you have tested that subsecuentent reloads doesn't retrieves all data again.

Re: Data Mismmatch

smilingjohn — Mon, 11 May 2020 07:05:27 GMT

@Durgadevikumar

Sales:
Load
'LOAD_DATE',
'Sales'
SQL SELECT *
FROM "Sales_IT".Sales
where ''LOAD_DATE'' > '10-FEB-20';

I tried by giving the manaully entry and it should load only 100 records (as on 11-May -20 there are only 100 records in the oracle DB ) , And this is loading complete data

Re: Data Mismmatch

smilingjohn — Mon, 11 May 2020 07:11:53 GMT

One more thing I noticed is that the load date in DB appears to be like

and after reload in QLikview the load date turns to be like below

Re: Data Mismmatch

rubenmarin — Mon, 11 May 2020 07:28:12 GMT

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?

Re: Data Mismmatch

smilingjohn — Mon, 11 May 2020 07:33:59 GMT

Hi @rubenmarin

This gave an erorr while i was reloading the app

Re: Data Mismmatch

smilingjohn — Mon, 11 May 2020 07:48:28 GMT

@rubenmarin

This works

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

after loading this i got exactly 100 records

Re: Data Mismmatch

rubenmarin — Mon, 11 May 2020 07:56:52 GMT

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.

Re: Data Mismmatch

smilingjohn — Mon, 11 May 2020 08:07:49 GMT

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 .

Re: Data Mismmatch

rubenmarin — Mon, 11 May 2020 08:21:14 GMT

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.

Re: Data Mismmatch

smilingjohn — Mon, 11 May 2020 09:37:58 GMT

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

Re: Data Mismmatch

rubenmarin — Mon, 11 May 2020 09:44:38 GMT

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.

Re: Data Mismmatch

smilingjohn — Mon, 11 May 2020 10:08:35 GMT

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

Re: Data Mismmatch

rubenmarin — Mon, 11 May 2020 11:01:27 GMT

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-files-incremental-load.htm

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/1486886