topic Re: Incremental load error in QlikView

Incremental load error

yadav_anil782 — Wed, 18 Nov 2015 09:57:41 GMT

Dear All ,

i'm using this script for incrimental but this script giving error and i'm stuck to find error , if can anybody help then plz help to find the error

LOAD USER_NAME,

USER_ID,

LOGIN_TIME,

LOGIN_COUNT

FROM

(qvd);

last_updated_date:

LOAD

max(LOGIN_TIME) as max_date

Resident xx_login_rev_view;

let vmax =Peek(max_date,0,'last_updated_date') ;

drop Table xx_login_rev_view;

incrimental:

LOAD

USER_NAME,

USER_ID,

LOGIN_TIME,

LOGIN_COUNT

FROM APPS."XX_LOGIN_REV_VIEW"

where LOGIN_TIME > $(last_updated_date);

Concatenate

LOAD USER_NAME,

USER_ID,

LOGIN_TIME,

LOGIN_COUNT

FROM

(qvd);

store incrimental into;

drop Table incrimental;

Re: Incremental load error

sunny_talwar — Wed, 18 Nov 2015 09:58:24 GMT

What is the exact error you are seeing? Can share the log file?

Re: Incremental load error

yadav_anil782 — Wed, 18 Nov 2015 10:07:27 GMT

Hi Sunny ,

i attached script , i marked red on where condition as per my practice this is correct but it is showing error

Re: Incremental load error

tresB — Wed, 18 Nov 2015 10:10:41 GMT

Try putting quotes around variable like:

where LOGIN_TIME > '$(last_updated_date)' ;

Re: Incremental load error

Wed, 18 Nov 2015 10:37:05 GMT

Please check the date formatting while doing the comparison for field LOGIN_TIME and variable which you have created. Also as tresesco mentioned mentioned you need to put variable as mentioned.

Re: Incremental load error

Anonymous — Wed, 18 Nov 2015 10:43:54 GMT

use vmax here:

incrimental:

LOAD

USER_NAME,

USER_ID,

LOGIN_TIME,

LOGIN_COUNT

FROM APPS."XX_LOGIN_REV_VIEW"

where LOGIN_TIME > $(last_updated_date); //error is here

try this:

incrimental:

LOAD

USER_NAME,

USER_ID,

LOGIN_TIME,

LOGIN_COUNT

FROM APPS."XX_LOGIN_REV_VIEW"

where LOGIN_TIME > $(vmax);

Re: Incremental load error

jonathandienst — Wed, 18 Nov 2015 10:48:39 GMT

It looks like the 'incremental' table is being loaded from database. In that case, you need:

incrimental:

SQL SELECT

USER_NAME,

USER_ID,

LOGIN_TIME,

LOGIN_COUNT

FROM APPS."XX_LOGIN_REV_VIEW"

where LOGIN_TIME > '$(vmax)';

you will also need to CONNECT statement before the load statement to make a connection to the database.

Re: Incremental load error

yadav_anil782 — Wed, 18 Nov 2015 11:23:30 GMT

HI,

date format is same , the problem with where condition.

Re: Incremental load error

Anonymous — Wed, 18 Nov 2015 11:28:26 GMT

Did you try my suggestion?

Re: Incremental load error

yadav_anil782 — Wed, 18 Nov 2015 11:32:07 GMT

Dear Jonathan,

i tried the same which you suggested but still its not working , plz find the attached

Re: Incremental load error

yadav_anil782 — Wed, 18 Nov 2015 11:34:52 GMT

Hi Balraj,

still not working

Re: Incremental load error

Wed, 18 Nov 2015 11:36:06 GMT

Hi ,

May i know the data type of LOGIN_TIME in your source, and from where you are extracting your data.

oracle or MS Sql. you also need to check the same query should work in your source system.

Re: Incremental load error

Anonymous — Wed, 18 Nov 2015 11:40:44 GMT

What about format??

after using max() this variable vmax would be having number value...

make sure LOGIN_TIME, shud be a number

where num(LOGIN_TIME)>$(vmax)

Hope this will help!!

Re: Incremental load error

yadav_anil782 — Wed, 18 Nov 2015 11:42:47 GMT

Dear Sunny,

i attached the final script error

Re: Incremental load error

tresB — Wed, 18 Nov 2015 11:54:47 GMT

Try putting a space after into, like:

store incrimental into [C:\Qlikview\QlikView Project\gl\qvd\xx_login_rev_view.qvd];

Re: Incremental load error

maxgro — Wed, 18 Nov 2015 12:05:39 GMT

add a trace of vmax variable

and post the trace and some values of LOGIN_TIME

they should have the same format, if vmax is a number --> LOGIN_TIME a number, etc......

the .qvw log could be also useful if you have any error

Re: Incremental load error

Anonymous — Wed, 18 Nov 2015 12:12:15 GMT

Hello,

have you tried to debug the script and check what the variable is? I mean, it may be a database error because it doesn't understand the date format of the variable.

Re: Incremental load error

jonathandienst — Wed, 18 Nov 2015 12:19:45 GMT

I am afraid the screenshots are not very informative. We need precise details of the error, not the line you think is causing it.

Do you have the reload log? Attach the last 20 lines or so. If there is no reload log, go to Document Properties and on the General tab, check the "Generate Logfile" button. Reload the document and send a clip of the last 20 lines or so of the log file. It will be located in the same folder as the document, with the same name except with a .log extension.

You might need to modify this line:

let vmax =Peek(max_date,0,'last_updated_date') ;

let vmax = date(Peek(max_date,0,'last_updated_date'));

Re: Incremental load error

Mark_Little — Wed, 18 Nov 2015 12:21:19 GMT

Hi,

Yer I would be approaching this now with the trace command or debugging load.

Make sure your variable is populated and formatted as you can expected.

Take it a step at a time

LOAD USER_NAME,

USER_ID,

LOGIN_TIME,

LOGIN_COUNT

FROM

(qvd);

last_updated_date:

LOAD

max(LOGIN_TIME) as max_date

Resident xx_login_rev_view;

let vmax =Peek(max_date,0,'last_updated_date') ;

drop Table xx_login_rev_view;

Trace $(vmax);

Exit script

Then if this all seems correct then load the next part where you are matching the variable on load.

Mark

Re: Incremental load error

yadav_anil782 — Wed, 18 Nov 2015 12:29:39 GMT

plz find the log details

18-11-2015 17:09:56: Execution started.

18-11-2015 17:09:56: QlikView Version:11.20.12904.0

18-11-2015 17:09:56: CPU Target x64

18-11-2015 17:09:56: Operating System Windows 8.1 Pro (64 bit edition)

18-11-2015 17:09:56: Wow64 mode Not using Wow64

18-11-2015 17:09:56: MDAC Version 6.3.9600.16384

18-11-2015 17:09:56: MDAC Full Install Version 6.3.9600.16384

18-11-2015 17:09:56: PreferredCompression 2

18-11-2015 17:09:56: EnableParallelReload 1

18-11-2015 17:09:56: ParallelizeQvdLoads 1

18-11-2015 17:09:56: AutoSaveAfterReload 0

18-11-2015 17:09:56: BackupBeforeReload 0

18-11-2015 17:09:56: EnableFlushLog 0

18-11-2015 17:09:56: SaveInfoWhenSavingFile 0

18-11-2015 17:09:56: UserLogfileCharset 1200

18-11-2015 17:09:56: OdbcLoginTimeout -1

18-11-2015 17:09:56: OdbcConnectionTimeout -1

18-11-2015 17:09:56: ScriptWantsDbWrite false

18-11-2015 17:09:56: ScriptWantsExe false

18-11-2015 17:09:56: LogFile CodePage Used: 1200

18-11-2015 17:09:56: Reload Executed By M3MGROUP\anil.kumar

18-11-2015 17:09:56: Process Executing: QlikView Desktop

18-11-2015 17:09:56: Process ID: 3124

18-11-2015 17:09:56: 0002 SET ThousandSep=','

18-11-2015 17:09:56: 0003 SET DecimalSep='.'

18-11-2015 17:09:56: 0004 SET MoneyThousandSep=','

18-11-2015 17:09:56: 0005 SET MoneyDecimalSep='.'

18-11-2015 17:09:56: 0006 SET MoneyFormat='₹ #,##0.00;₹ -#,##0.00'

18-11-2015 17:09:56: 0007 SET TimeFormat='hh:mm:ss'

18-11-2015 17:09:56: 0008 SET DateFormat='DD-MM-YYYY'

18-11-2015 17:09:56: 0009 SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]'

18-11-2015 17:09:56: 0010 SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

18-11-2015 17:09:56: 0011 SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

18-11-2015 17:09:56: 0013 ODBC CONNECT*UserId*Password*

18-11-2015 17:09:57: 0038 xx_login_rev_view:

18-11-2015 17:09:57: 0039 LOAD USER_NAME,

18-11-2015 17:09:57: 0040 USER_ID,

18-11-2015 17:09:57: 0041 LOGIN_TIME,

18-11-2015 17:09:57: 0042 LOGIN_COUNT

18-11-2015 17:09:57: 0043 FROM

18-11-2015 17:09:57: 0044

18-11-2015 17:09:57: 0045 (qvd)

18-11-2015 17:09:57: 4 fields found: USER_NAME, USER_ID, LOGIN_TIME, LOGIN_COUNT, 33,940 lines fetched

18-11-2015 17:09:57: 0048 last_updated_date:

18-11-2015 17:09:57: 0049 LOAD

18-11-2015 17:09:57: 0050 max(LOGIN_TIME) as max_date

18-11-2015 17:09:57: 0051 Resident xx_login_rev_view

18-11-2015 17:09:57: 1 fields found: max_date, 1 lines fetched

18-11-2015 17:09:57: 0053 let vmax =Peek(max_date,0,'last_updated_date')

18-11-2015 17:09:57: 0055 drop Table xx_login_rev_view

18-11-2015 17:09:57: 0057 incrimental:

18-11-2015 17:09:57: 0058 SQL SELECT

18-11-2015 17:09:57: 0059 USER_NAME,

18-11-2015 17:09:57: 0060 USER_ID,

18-11-2015 17:09:57: 0061 LOGIN_TIME,

18-11-2015 17:09:57: 0062 LOGIN_COUNT

18-11-2015 17:09:57: 0063 FROM APPS."XX_LOGIN_REV_VIEW"

18-11-2015 17:09:57: 0064 where LOGIN_TIME > ''

18-11-2015 17:10:11: 4 fields found: USER_NAME, USER_ID, LOGIN_TIME, LOGIN_COUNT, 0 lines fetched

18-11-2015 17:10:11: 0066 Concatenate

18-11-2015 17:10:11: 0067 LOAD USER_NAME,

18-11-2015 17:10:11: 0068 USER_ID,

18-11-2015 17:10:11: 0069 LOGIN_TIME,

18-11-2015 17:10:11: 0070 LOGIN_COUNT

18-11-2015 17:10:11: 0071 FROM

18-11-2015 17:10:11: 0072

18-11-2015 17:10:11: 0073 (qvd)

18-11-2015 17:10:11: 4 fields found: USER_NAME, USER_ID, LOGIN_TIME, LOGIN_COUNT, 33,940 lines fetched

18-11-2015 17:10:11: 0075 store incrimental into

18-11-2015 17:10:11: Error: Syntax error, missing/misplaced FROM:

18-11-2015 17:10:11: store incrimental into

18-11-2015 17:13:10: 0076 drop Table incrimental

18-11-2015 17:13:10: Execution finished.