topic Re: MySQL DB load incremental in QlikView

MySQL DB load incremental

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

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

Re: MySQL DB load incremental

DesmoArka — Thu, 07 May 2020 12:14:47 GMT

Hi,

try to read this:

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

May you don't check primary key.

I hope this help.

Re: MySQL DB load incremental

smilingjohn — Thu, 07 May 2020 14:45:08 GMT

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;

Re: MySQL DB load incremental

DesmoArka — Thu, 07 May 2020 15:00:13 GMT

@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"?

Re: MySQL DB load incremental

smilingjohn — Thu, 07 May 2020 15:32:32 GMT

Hi ,

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

Thanks for the reply .

Re: MySQL DB load incremental

DesmoArka — Thu, 07 May 2020 16:03:04 GMT

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'

Re: MySQL DB load incremental

smilingjohn — Thu, 07 May 2020 16:06:46 GMT

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

Re: MySQL DB load incremental

DesmoArka — Thu, 07 May 2020 16:22:01 GMT

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

Re: MySQL DB load incremental

smilingjohn — Thu, 07 May 2020 16:42:16 GMT

@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

Re: MySQL DB load incremental

DesmoArka — Fri, 08 May 2020 07:07:34 GMT

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.

Re: MySQL DB load incremental

smilingjohn — Fri, 08 May 2020 08:05:16 GMT

@DesmoArka

Thanks for the reply , But this doesnot work i tried may be the mYSQL query in qlikview requires different date format .

I tried your solution but still the double records problem .

Re: MySQL DB load incremental

DesmoArka — Fri, 08 May 2020 08:14:57 GMT

Let me know...

You have more records ok, but mySql query works fine? or mySql query extracts all records and so you have double records?

Just to understand if the problem is in qlik or in mySql 🙂

Re: MySQL DB load incremental

smilingjohn — Fri, 08 May 2020 08:28:08 GMT

HI @DesmoArka

I have many records in mysql Db

trying manaul date also gives the double data yes it fetches all the data .