script error when comparing dates in QV Incremental Load

PRS — Sat, 16 Nov 2024 00:58:57 GMT

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.

Re: script error when comparing dates in QV Incremental Load

dplr-rn — Wed, 25 Mar 2020 15:05:45 GMT

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

Re: script error when comparing dates in QV Incremental Load

PRS — Wed, 25 Mar 2020 15:57:21 GMT

Hello Dilip,

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

Thanks for your help.

Re: script error when comparing dates in QV Incremental Load

dplr-rn — Wed, 25 Mar 2020 16:29:05 GMT

i think convert(datetime,

https://www.w3schools.com/sql/func_sqlserver_convert.asp

topic Re: script error when comparing dates in QV Incremental Load in QlikView

script error when comparing dates in QV Incremental Load

Re: script error when comparing dates in QV Incremental Load

Re: script error when comparing dates in QV Incremental Load

Re: script error when comparing dates in QV Incremental Load