topic Re: Incremental load in QlikView

Incremental load

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

I have a field named changed_date it is in the format as mentioned in image.

I need to work on incremental load in Sql extract file.

CRTDATA_DBO_OFFENSE_RecentDate:

LOAD TIMESTAMP(max(CHANGED_DATE)) as CRTDATA_DBO_OFFENSE_MaxDate Resident CRTDATA_DBO_OFFENSE;

LET vCRTDATA_DBO_OFFENSE_MaxDate = Peek('CRTDATA_DBO_OFFENSE_MaxDate',0,'CRTDATA_DBO_OFFENSE_RecentDate');

Load *;

SQL SELECT *

FROM fcedata.dbo.OFFENSE WHERE CHANGED_DATE > ('$(CRTDATA_DBO_OFFENSE_RecentDate)');

Here on SQL where condition getting the error not taking the date format.Please help me to do incremetnal load on this table with changed date.

Re: Incremental load

Anil_Babu_Samineni — Mon, 22 Aug 2016 07:24:27 GMT

What error you are facing?

Load *;

SQL SELECT *

FROM fcedata.dbo.OFFENSE WHERE CHANGED_DATE > "$(CRTDATA_DBO_OFFENSE_RecentDate)";

And i didn't find your field CRTDATA_DBO_OFFENSE_RecentDate

Re: Incremental load

marcus_sommer — Mon, 22 Aug 2016 07:30:36 GMT

You need to check if your variable contained the right value and the right format, maybe per:

trace '$(CRTDATA_DBO_OFFENSE_RecentDate)';

and if not you need to specify the right format within the timestamp-function which will without any specifying return the default timestamp-format.

- Marcus

Re: Incremental load

maxgro — Mon, 22 Aug 2016 10:35:54 GMT

Build a working query outside QlikView (if your db is sql server, you can use sql server management studio)

SELECT * FROM fcedata.dbo.OFFENSE WHERE CHANGED_DATE >= '2016-08-22 05:10:00.700'

Then try to match the format of your variable with the format of the datetime in the working query

For example this works in Qlik with my sql server (change the format if your is different)

ODBC CONNECT32 TO ..............;

// i want all rec >= year 2000

LET v=Timestamp(MakeDate(2000), 'YYYY-MM-DD hh:mm:ss.fff');

TRACE v=$(v);

T: LOAD *;

SQL SELECT * FROM "sisim_stat".dbo."md002_log"

where dat_data >= '$(v)';

STORE T into T.qvd (qvd);

DROP Table T;

// read max stored (in qvd) timestamp

tmp: load max(dat_data) as max_dat_data from T.qvd (qvd);

// format the variable v as sql server

// subtract 1 day just to test

LET v= Timestamp(Peek('max_dat_data')-1, 'YYYY-MM-DD hh:mm:ss.fff');

TRACE v=$(v);

T: LOAD *;

SQL SELECT * FROM "sisim_stat".dbo."md002_log"

where dat_data >= '$(v)';

Re: Incremental load

bhavvibudagam — Mon, 22 Aug 2016 11:34:53 GMT

Hi,

As you said i have tried to match the format of datetime.But getting the error as

ODBC CONNECT TO [PDRMSBKUP;DBQ=PDRMSBKUP ] (XUserId is KMAKLZVNTTcKGYdNSDdcC, XPassword is bDaIOZVNTTcKGYdNSDdKL);

SQL SELECT MODTIME

FROM LEADS.ARMAIN where MODTIME > '8/15/2016 6:36:00 AM';

Re: Incremental load

maxgro — Mon, 22 Aug 2016 11:54:28 GMT

In your previous post the database was Sql Server. In the last post Oracle.

Every db has different date/time functions and format, you can't mix them.

Could you clarify ?

If you use an Oracle timestamp column, you can try with

select * from ..........

where MODTIME >= to_timestamp('$(v)', 'MM/DD/YYYY HH:MI:SS AM')

Yu have to format the Qlikview v variable as

'6/15/2016 6:36:00 AM'

Re: Incremental load

bhavvibudagam — Mon, 22 Aug 2016 12:31:18 GMT

Hi,Thank you soo much for your reply its working fine.

In my date field I have both AM and PM then that time How i have to mention , like qlik can we use TT in oracle.

Re: Incremental load

maxgro — Mon, 22 Aug 2016 12:49:01 GMT

In Oracle you can use AM or PM to describe the time in AM/PM format

SELECT TO_TIMESTAMP ('09-25-2016 2:10:10.123000 PM', 'MM-DD-YYYY HH:MI:SS.FF AM') FROM DUAL;

SELECT TO_TIMESTAMP ('09-25-2016 2:10:10.123000 PM', 'MM-DD-YYYY HH:MI:SS.FF PM') FROM DUAL;

Same result

In Qlik you can use tt

https://help.qlik.com/en-US/sense/3.0/Subsystems/Hub/Content/Introduction/conventions-number-time-formats-2.htm