Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
sekharQV
Creator
Creator

script error when comparing dates in QV Incremental Load

Hi All,

I hope you are doing well.

I am facing the below  error when trying to compare dates in Incremental Load implementation.

2020-03-25 15:45:26 0197 drop table MaxKey
2020-03-25 15:45:26 0202 cp_data2:
2020-03-25 15:45:26 0203 SQL SELECT CraneID,
2020-03-25 15:45:26 0204 cp_id,
2020-03-25 15:45:26 0205 parameter,
2020-03-25 15:45:26 0206 datetime,
2020-03-25 15:45:26 0207 RowID,
2020-03-25 15:45:26 0208 value
2020-03-25 15:45:26 0209 FROM cp_data
2020-03-25 15:45:26 0210
2020-03-25 15:45:26 0211 where datetime> '30/3/2017 6:01:28 AM'
2020-03-25 15:45:26 Error: Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
2020-03-25 15:45:28 Execution Failed
2020-03-25 15:45:29 Execution finished.

I am not sure conversion is happening here.

Below is the code I have tried and attached the log file for your reference. 

 Kindly help me to resolve the issue.

 

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/M/YYYY';
SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

set _qvd_path = '..\..\..\QVD\QVDfolder\'; //?? The QVD filename

set _qvd_name = 'cp_data2'; //?? The name of the QV table we are loading on the Data Load tab

set vKeyField = RowID; //?? Data Primary Key

set vUpdateField = datetime;

OLEDB CONNECT TO ***

let vFileLen = FileSize('$(_qvd_path)$(_qvd_name).qvd');

trace '>> QVD Size: ' $(vFileLen);

if len('$(vFileLen)') = 0 then

trace 'Full reload begins...';

$(_qvd_name):
SQL SELECT
CraneID,
cp_id,
parameter,
datetime,
RowID,
value
FROM cp_data;

let vRows = NoOfRows('$(_qvd_name)');


// Max Date field value into a QVD for the future reload

NewKey:
load
num#(max(FieldValue('$(vUpdateField)', RecNo()))) as Key
AutoGenerate num#(FieldValueCount('$(vUpdateField)'));

let vNewKey = peek('Key', 0, 'NewKey');


let vKeyQVD = 'MaxKey';

 


$(vKeyQVD):
load
'$(vNewKey)' as MaxKey,
'$(vRows)' as [# of Rows Inserted],
date(now()) as [Reload DateTime]
AutoGenerate(1)
;

 


STORE $(vKeyQVD) into $(_qvd_path)$(vKeyQVD).qvd(qvd);

drop Table NewKey;

 

// Store table data into QVD


store $(_qvd_name) into $(_qvd_path)$(_qvd_name).qvd(qvd);

drop table $(_qvd_name);

//5. Reset variables

LET vNewKey = null();
LET vMaxKey = Null();
LET vRows = null();

 

 


ELSEIF len('$(vFileLen)') > 0 then

TRACE 'Incremental reload begins...';

set vKeyQVD = 'MaxKey';

$(vKeyQVD):
load
Timestamp(MaxKey) as MaxKey
from
$(_qvd_path)$(vKeyQVD).qvd(qvd);


let vMaxKey = Peek('MaxKey');

trace '>>> Last Max Key value stored: ' & $(vMaxKey);

drop table $(vKeyQVD);

//Perform incremental reload

$(_qvd_name):
SQL SELECT CraneID,
cp_id,
parameter,
datetime,
RowID,
value
FROM cp_data

where $(vUpdateField)> '$(vMaxKey)';

let vRows = NoOfRows('$(_qvd_name)');

trace 'Total Rows:' $(vRows);

Concatenate($(_qvd_name))
load
CraneID,
cp_id,
parameter,
datetime,
RowID,
value
from
$(_qvd_path)$(_qvd_name).qvd(qvd)
Where not Exists ([$(vKeyField)]);

// To delete records inner join key field with the QVD table.

inner join($(_qvd_name))
LOAD
[$(vKeyField)];

sql select [$(vKeyField)]
from cp_data;

trace 'Storing table data into QVD';

store $(_qvd_name) into $(_qvd_path)$(_qvd_name).qvd(qvd);

// 7. Store max Primary Key ID for the future incremental reload (QVD)

NewKey:
load
num#(max(FieldValue('$(vUpdateField)', RecNo()))) as Key
AutoGenerate num#(FieldValueCount('$(vUpdateField)'));


let vNewKey = peek('Key', 0, 'NewKey');


let vKeyQVD = 'MaxKey';

let vRows = NoOfRows('$(_qvd_name)');

$(vKeyQVD):
load
'$(vNewKey)' as MaxKey,
'$(vRows)' as [# of Rows Inserted],
date(now()) as [Reload DateTime]
AutoGenerate(1) ;

drop table NewKey;

STORE $(vKeyQVD) into $(_qvd_path)$(vKeyQVD).qvd(qvd);

//drop table $(_qvd_name);


EXIT SCRIPT;

 

Thanks in Advance,

Sekhar.

Labels (2)
1 Solution

Accepted Solutions
3 Replies
dilipranjith
Partner
Partner

the where statement

where datetime> '30/3/2017 6:01:28 AM'

is within a sql statement.

so you need convert the text to date format in the db. and that depends on the db

e.g. if your db is oracle datetime> to_date('30/3/2017 6:01:28 AM','DD/MM/YYYY HH:MI:SS')

 

sekharQV
Creator
Creator
Author

Hello Dilip,

Our source DB is SQL server. Can you please suggest relevant date function with the format.

Thanks for your help.