MySQL DB load incremental

[smilingjohn]

Hi All, 

I am trying to Achive incremental load from MySQL DB and it is not working need your advice to resolve this . 

In the MySQL DB the data type for date is "DateTime"  I have fectehd the data and cinverted to QVD and also added Last updated . 

and incremenatl procedure but after completing the records are getting double ? 

Can please someone help me on this 

Thanks in Advance 

[DesmoArka]

Hi,

try to read this:

https://help.qlik.com/it-IT/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/QVD_Incremental...

May you don't check primary key.

I hope this help.

[smilingjohn]

Can please someone check if the script is correct . the records are doubled 

Date formate in mySQL is like     2020-04-21 07:55:12

TCS:
Load
Sales
LOAD_DATE
FROM
[..\QVD\TCS.qvd]
(qvd);

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

Concatenate (TCS)
Load
'Sales'
'LOAD_DATE'
SQL SELECT *
FROM 'Sales_item'.TCS
where 'LOAD_DATE' > Date_Format('$(vLast_Updated_Date)', '%m/%d/%y');
store * from TCS into $(vPath)\TCS.qvd;
DROP Table TCS;

[DesmoArka]

---

@smilingjohn wrote:

Can please someone check if the script is correct . the records are doubled 

Date formate in mySQL is like     2020-04-21 07:55:12

TCS:
Load
Sales
LOAD_DATE
FROM
[..\QVD\TCS.qvd]
(qvd);

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

Concatenate (TCS)
Load
'Sales'
'LOAD_DATE'
SQL SELECT *
FROM 'Sales_item'.TCS
where 'LOAD_DATE' > Date_Format('$(vLast_Updated_Date)', '%m/%d/%y');
store * from TCS into $(vPath)\TCS.qvd;
DROP Table TCS;

---

but if Your Date formate in mySQL is like     2020-04-21 07:55:12

why you are using '%m/%d/%y' and not "%Y-%m-%d"?

[smilingjohn]

Hi , 

I tired with that also its not working . Should i use double quotes ?

Thanks for the reply . 

[DesmoArka]

I don't know but you can try.

If you have double records , maybe you can try to change max(LOAD_DATE),'MM/DD/YYYY' also.

change 'MM/DD/YYYY' with  'YYYY-MM-DD'

[smilingjohn]

@DesmoArka  

Tried by changing the format of max(loadDate),'YYYY-MM-DD' but still after the complete reload process the records are double I dont know why is it so ?

[DesmoArka]

---

@smilingjohn wrote:

@DesmoArka  

Tried by changing the format of max(loadDate),'YYYY-MM-DD' but still after the complete reload process the records are double I dont know why is it so ?

---

but have you try to write manually some date?

something like where 'LOAD_DATE' > Date_Format("2020-04-15", "%Y-%m-%d"); works or not?

[smilingjohn]

@DesmoArka 

 trued giving manaul date but it still loadin the records twice . I dont understand what is happening . 

I have done the incremental load when the data was from Oracle but really strugling to achive this in mySQL DB 

[DesmoArka]

hi,

i tried your query and i have problem too.

This query works fine:

SELECT *
FROM myTest
where `dt` > '2020-01-20';

For field name use  ` not '

So maybe it will work:

SELECT *
FROM myTest
where `dt` > '$(vLast_Updated_Date)';

if you format vLast_Updated_Date with YYYY-MM-DD.

I hope this helps you.