Incremental load

[bhavvibudagam]

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.

[Anil_Babu_Samineni]

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

[marcus_sommer]

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

[maxgro]

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)';

[bhavvibudagam]

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';

[maxgro · Accepted solution]

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'

[bhavvibudagam]

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.

[maxgro]

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