Partial reload Script (Not working as expected)

[#1]

Hi All,

I am trying for below:

1. 18 Months data are divided into 3 QVD files and each hold 6 months of data. FACT_SALARY_DATA_61.QVD  (Latest 6 months data), FACT_SALARY_DATA_62.QVD  (2nd 6 months data), FACT_SALARY_DATA_63.QVD (3rd 6 months data)
2. I am using both FULL Load and Partial Reload. When in morning we will be reloading and publishing, we will do full reload and will load data of FACT_SALARY_DATA_61.QVD in application.
3. There is a variable vFile=12 and 18 and Partial Reload Button. If user Select vFile=12 and click on Partial Reload button, below script will ADD ONLY  FACT_SALARY_DATA_62.QVD to existing data which is having only FACT_SALARY_DATA_61.QVD.
4. I have a User QVD table where I can update flag after each partial load and so avoid duplicate load. So when Step 3 performed, I will be updating LoadFlag=1 for NumberOfMonths=12
5. Same way for 18 months.

Now I am facing few challenges and  not sure how to handle in below script. During Partial Reload underlined conditions are not working (Evaluating) . Why? Someone please could you review the script and let me know what to do in my case to fix this issue.

  IF $(vFile)=12 AND v12NumberOfMonths=12 AND v12LoadFlag=0 THEN

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='D/MM/YYYY';

SET TimestampFormat='D/MM/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 HidePrefix = '_';

  LOAD_ON_REQUEST_CONFIG_12:

  NOCONCATENATE LOAD *

  FROM $(vFactQVDStoragePath)\LOAD_ON_REQUEST_CONFIG_DATA.QVD (qvd)

  WHERE NumberOfMonths=12;

  LET v12NumberOfMonths=PEEK('NumberOfMonths',0,'LOAD_ON_REQUEST_CONFIG_12');

  LET v12LoadFlag=PEEK('LoadFlag',0,'LOAD_ON_REQUEST_CONFIG_12');

  DROP TABLE LOAD_ON_REQUEST_CONFIG_12;

  LOAD_ON_REQUEST_CONFIG_18:

  NOCONCATENATE LOAD *

  FROM $(vFactQVDStoragePath)\LOAD_ON_REQUEST_CONFIG_DATA.QVD (qvd)

  WHERE NumberOfMonths=18;

  LET v18NumberOfMonths=PEEK('NumberOfMonths',0,'LOAD_ON_REQUEST_CONFIG_18');

  LET v18LoadFlag=PEEK('LoadFlag',0,'LOAD_ON_REQUEST_CONFIG_18');

  DROP TABLE LOAD_ON_REQUEST_CONFIG_18;

IF NOT IsPartialReload() THEN //Full Reload and load data from FACT_SALARY_DATA_61.QVD (qvd)

/*-----------------------**************************----------------------------------*/

DIM_EMP_DATA:

LOAD  

       EMPKey AS _EMPKey,

       EMPID,

       EMPNAME

FROM $(vDimQVDStoragePath)\DIM_EMP_DATA.QVD (qvd);

DIM_DEPARTMENT_DATA:

LOAD

       DEPTKey AS _DEPTKey,

       DEPTID,

       DEPTNAME

FROM $(vDimQVDStoragePath)\DIM_DEPARTMENT_DATA.QVD (qvd);

FACT_SALARY_DATA:

LOAD EMPKey      AS _EMPKey,

     DEPTKey     AS _DEPTKey,

     [SALARY_AMT]

FROM $(vFactQVDStoragePath)\FACT_SALARY_DATA_61.QVD (qvd);

/*-----------------------**************************----------------------------------*/

ELSE

  IF $(vFile)=12 AND v12NumberOfMonths=12 AND v12LoadFlag=0 THEN

  LOAD_ON_REQUEST_CONFIG_DATA:

  REPLACE LOAD

  RunDateTime,

  NumberOfMonths,

  IF (NumberOfMonths=12 AND LoadFlag=0,1,LoadFlag) AS LoadFlag

  FROM $(vFactQVDStoragePath)\LOAD_ON_REQUEST_CONFIG_DATA.QVD (qvd);

  STORE LOAD_ON_REQUEST_CONFIG_DATA INTO $(vFactQVDStoragePath)\LOAD_ON_REQUEST_CONFIG_DATA.QVD;

  DROP TABLE LOAD_ON_REQUEST_CONFIG_DATA;

  ADD ONLY LOAD

     EMPKey      AS _EMPKey,

     DEPTKey     AS _DEPTKey,

     [SALARY_AMT]

   FROM $(vFactQVDStoragePath)\FACT_SALARY_DATA_62.QVD (qvd);

  ELSEIF $(vFile)=18 AND v18NumberOfMonths=18 AND v18LoadFlag=0 THEN

IF v12NumberOfMonths=12 AND v12LoadFlag=0 THEN

  LOAD_ON_REQUEST_CONFIG_DATA:

  REPLACE LOAD

  RunDateTime,

  NumberOfMonths,

  IF ((NumberOfMonths=12 OR NumberOfMonths=18) AND LoadFlag=0,1,LoadFlag) AS LoadFlag

  FROM $(vFactQVDStoragePath)\LOAD_ON_REQUEST_CONFIG_DATA.QVD (qvd);

  STORE LOAD_ON_REQUEST_CONFIG_DATA INTO $(vFactQVDStoragePath)\LOAD_ON_REQUEST_CONFIG_DATA.QVD;

  DROP TABLE LOAD_ON_REQUEST_CONFIG_DATA;

  ADD ONLY LOAD

     EMPKey      AS _EMPKey,

     DEPTKey     AS _DEPTKey,

     [SALARY_AMT]

     FROM $(vFactQVDStoragePath)\FACT_SALARY_DATA_62.QVD (qvd);

  ADD ONLY LOAD

     EMPKey      AS _EMPKey,

     DEPTKey     AS _DEPTKey,

     [SALARY_AMT]

     FROM $(vFactQVDStoragePath)\FACT_SALARY_DATA_63.QVD (qvd);

  ELSE

  LOAD_ON_REQUEST_CONFIG_DATA:

  REPLACE LOAD

  RunDateTime,

  NumberOfMonths,

  IF ((NumberOfMonths=18) AND LoadFlag=0,1,LoadFlag) AS LoadFlag

  FROM $(vFactQVDStoragePath)\LOAD_ON_REQUEST_CONFIG_DATA.QVD (qvd);

  STORE LOAD_ON_REQUEST_CONFIG_DATA INTO $(vFactQVDStoragePath)\LOAD_ON_REQUEST_CONFIG_DATA.QVD;

  DROP TABLE LOAD_ON_REQUEST_CONFIG_DATA;

  ADD ONLY LOAD

     EMPKey      AS _EMPKey,

     DEPTKey     AS _DEPTKey,

     [SALARY_AMT]

     FROM $(vFactQVDStoragePath)\FACT_SALARY_DATA_63.QVD (qvd);

  END IF;

  ELSE

  LOAD

  MsgBox ('Data has been Loaded');

  END IF;

END IF;

[its_anandrjs]

Hi,

Try the key words Add Or Replace in the load script below the start of the script.

Regards

Anand

[#3]

Anand,

Did you check my code? It is already there.. If still you found something wrong could you please underline it in the above code.

[its_anandrjs]

Ok it is there and how you reload your data by file menu or by reload button, did you reload it with file menu and partial reload command.

Regards

Anand

[#5]

I tried by both the way. From File menu as well as Reload Button .

If I comment the condition v12NumberOfMonths=12 AND v12LoadFlag=0  from   IF $(vFile)=12 AND v12NumberOfMonths=12 AND v12LoadFlag=0 THEN then It is reloading else just executing for 4 seconds and not loading anything.

Note: I checked in QVD file. it is v12LoadFlag=0 for v12NumberOfMonths=12