topic incremental load issue in QlikView

incremental load issue

Wed, 11 Sep 2013 11:37:44 GMT

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.

Re: incremental load issue

tresB — Wed, 11 Sep 2013 11:44:01 GMT

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.

Re: incremental load issue

israrkhan — Wed, 11 Sep 2013 12:20:07 GMT

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

Re: incremental load issue

Peter_Cammaert — Wed, 11 Sep 2013 12:42:02 GMT

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

Re: incremental load issue

Wed, 11 Sep 2013 12:56:37 GMT

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".

Re: incremental load issue

Carlos_Reyes — Wed, 11 Sep 2013 14:41:51 GMT

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