topic Re: Incremental Load: in QlikView

Incremental Load:

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am using below script for incremental load. Issue is happening while checking for Modified records.

I have verified date formats of 'modified_date' column from database and last update variable. Please check the attached image.

Please help to resolve the issue.

Script:

//Load from QVD

inc1:

LOAD TICKET_NUM,

APPLICATION,

"ASSIGNED_TO",

DESCRIPTION,

"TICKET_STATUS",

"OPEN_DATE",

"CLOSE_DATE",

MODIFIED_DATE

FROM

[...\abcd.qvd]

(qvd);

Last_Update:

LOAD Max(MODIFIED_DATE) as maxdate

resident inc1;

//Store max update date

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'MM/DD/YYYY hh:mm:ss TT');

DROP Table inc1;

//Incremental Load

INC2:

SQL SELECT "TICKET_NUM",

APPLICATION,

"ASSIGNED_TO",

DESCRIPTION,

"TICKET_STATUS",

"OPEN_DATE",

"CLOSE_DATE",

MODIFIED_DATE

FROM "GCOR_DI".GREDOPS

Where MODIFIED_DATE >$(Last_Update_Date);

Concatenate

LOAD TICKET_NUM,

APPLICATION,

"ASSIGNED_TO",

DESCRIPTION,

"TICKET_STATUS",

"OPEN_DATE",

"CLOSE_DATE",

MODIFIED_DATE

FROM

[...\gredops.qvd]

(qvd)

WHERE NOT EXISTS (TICKET_NUM);

//Store into QVD

STORE INC2 into gredops.qvd(qvd);

//Drop INC2

DROP Table INC2;

ERROR Message:

SQL##f - SqlState: S1000, ErrorCode: 933, ErrorMsg: [Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended

When filter is put under quotes

Where MODIFIED_DATE >'$(Last_Update_Date)';

Error Message is:

SQL##f - SqlState: S1000, ErrorCode: 1843, ErrorMsg: [Oracle][ODBC][Ora]ORA-01843: not a valid month.

Re: Incremental Load:

MK_QSL — Fri, 01 Apr 2016 08:43:42 GMT

Try

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'M/DD/YYYY hh:mm:ss TT');

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'M/D/YYYY hh:mm:ss TT');

Re: Incremental Load:

Chanty4u — Fri, 01 Apr 2016 08:44:50 GMT

try with dis?

Where MODIFIED_DATE >='$(Last_Update_Date)';

WHERE ModificationTime >='$(LastExecTime)'

AND ModificationTime <'$(Last_Update_Date)';

Re: Incremental Load:

kaushiknsolanki — Fri, 01 Apr 2016 08:47:52 GMT

Hi,

Make sure that Database takes date in 'MM/DD/YYYY hh:mm:ss TT' format.

What I can see is that Your variable is holding date format as 'DD/MM/YYYY hh:mm:ss TT' and thus the Sql gives you error that 30(From the variable) is not a valid month.

Regards,

Kaushik Solanki

Re: Incremental Load:

Fri, 01 Apr 2016 08:51:18 GMT

Hi Manish,

Thanks for your response, but it didn't work.

Error is: SQL##f - SqlState: S1000, ErrorCode: 1843, ErrorMsg: [Oracle][ODBC][Ora]ORA-01843: not a valid month.

Re: Incremental Load:

Kushal_Chawda — Fri, 01 Apr 2016 08:52:05 GMT

try this

Let Last_Update_Date = timestamp(peek('maxdate',0,'Last_Update'),'MM/DD/YYYY hh:mm:ss TT');

Check the Format of Variable in inputbox it should be in MM/DD/YYYY hh:mm:ss TT, then try below where condition

Where MODIFIED_DATE > to_char('$(Last_Update_Date)','MM/DD/YYY HH:MI:SS AM')

Re: Incremental Load:

Fri, 01 Apr 2016 09:04:13 GMT

This time I am getting SQL##f - SqlState: 22005, ErrorCode: 1722, ErrorMsg: [Oracle][ODBC][Ora]ORA-01722: invalid number

Re: Incremental Load:

Kushal_Chawda — Fri, 01 Apr 2016 09:19:08 GMT

Are you sure that MODIFIED_DATE format in database is MM/DD/YYYY hh:mm:ss TT?

if yes, then what is the value in Last_Update_Date variable, Just call the variable in text object & send the screenshot.

Re: Incremental Load:

Fri, 01 Apr 2016 09:20:46 GMT

Re: Incremental Load:

Kushal_Chawda — Fri, 01 Apr 2016 09:37:18 GMT

try this

Where MODIFIED_DATE > to_date('$(Last_Update_Date)','MM/DD/YYY HH:MI:SS AM')

make sure that MODIFIED_DATE is not a string in database if so, try

Where to_date(MODIFIED_DATE,'MM/DD/YYY HH:MI:SS AM') > to_date('$(Last_Update_Date)','MM/DD/YYY HH:MI:SS AM' )