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]

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.

[smilingjohn]

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 . 

[smilingjohn]

Hi Expertise any help on this ?

[Durgadevikumar]

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;

[rubenmarin]

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.

[smilingjohn]

@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 

[rubenmarin]

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.

[smilingjohn]

@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 

[smilingjohn]

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