incremental load issue

[#1]

Hi,

                 in log file the reload time variable is  not visible

DIM_SUMMARY_THRESHOLDS:
SQL SELECT "LOAD_DATE",
   "RED_THRESHOLD_PCT",
   "REGION_CODE",
   "YELLOW_THRESHOLD_PCT"
FROM "TBL_SUMMARY_THRESHOLDS" where "LOAD_DATE"< '$(vLastReloadTime)';
//Concatenate
//LOAD LOAD_DATE,
//     RED_THRESHOLD_PCT,
//     REGION_CODE,
//     YELLOW_THRESHOLD_PCT
//FROM
//C:\Users\ramanam\Desktop\DIM_SUMMARY_THRESHOLDS.qvd
//(qvd) where notExists();

STORE DIM_SUMMARY_THRESHOLDS into C:\Users\ramanam\Desktop\DIM_SUMMARY_THRESHOLDS.qvd.(qvd);

//DROP table DIM_SUMMARY_THRESHOLDS;

let vLastReloadTime = reloadtime();

Please see the attachment.

[tresesco]

Possibly, you are formatting the date in the variable which does not match with the one in the database. Look at the error:

9/11/2013 5:03:11 PM: 0021  FROM "TBL_SUMMARY_THRESHOLDS" where   "LOAD_DATE"> To_Date('9/11/2013 4:57:06 PM','DD-MMM-YYYY')

9/11/2013 5:03:12 PM:      Error: SQL##f - SqlState: S1000, ErrorCode: 1821, ErrorMsg: [Oracle][ODBC][Ora]ORA-01821: date format not recognized

Check the date format in the DB and make the variable date format similar to that. Then it should work.

[israrkhan]

always assign the reload time in If statement like below:

might be your last reload was not successful, and it assigned the reload time.

read the incremental load document.

if ScriptError=0 then

let LastReloadTime = reloadtime();

endif

[Peter_Cammaert]

Indeed, that will probably fix the current code.

I think you can improve on your strategy by getting the last reload date from the QVD instead. There are QlikView functions to do that, like QVDCreateTime().

If the QVD doesn't exist yet (QVDCreateTime() returns NULL), then you'll have to do a first full reload by omitting the WHERE clause altogether.

You could put a WHERE clause in a variable (its layout depends on your analysis of the existing QVD) and wrtie your SQL code as:

SQL SELECT ....

:

FROM "TBL_SUMMARY_THRESHOLDS" $(vWhere);

Good luck,

Peter

[#5]

Try to use TO_DATE Oracle function function in your load script.

//Connecting to oracleDB

ODBC CONNECT TO [???] (XUserId is ???, XPassword is ???);

// Assign value to DateTime variable

let vDateTime=Timestamp#('10/1/2011 1:00:00','MM/DD/YYYY hh:mm:ss');

SQL SELECT Field1,

    Field2,

    CREATETIME

FROM CHECKS."CHECK_LIST"

where CREATETIME>To_DATE('$(vDateTime)','MM/DD/YYYY hh:mi:ss');

// In SQL where statement use TO_DATE function with DateTime format.

Please mention that Oracle TO_DATE function use "mi" for minutes in format string instead "mm".

[Carlos_Reyes]

You have to make sure the database field format is equal to your varible format, sometimes you have to use the TRUNCATE function for the Database Field:

TRUNC(LAST_UPDATE_DATE) >= TO_DATE('$(vMonthStart)', 'MM/DD/YYYY')