Partial reload Script (Not working as expected)

Wed, 08 Apr 2015 04:14:41 GMT

Hi All,

I am trying for below:

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)
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.
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.
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
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;

Re: Partial reload Script (Not working as expected)

its_anandrjs — Wed, 08 Apr 2015 04:18:05 GMT

Hi,

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

Regards

Anand

Re: Partial reload Script (Not working as expected)

Wed, 08 Apr 2015 04:21:17 GMT

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.

Re: Partial reload Script (Not working as expected)

its_anandrjs — Wed, 08 Apr 2015 04:32:23 GMT

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

Re: Partial reload Script (Not working as expected)

Wed, 08 Apr 2015 04:44:50 GMT

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

topic Re: Partial reload Script (Not working as expected) in QlikView

Partial reload Script (Not working as expected)

Re: Partial reload Script (Not working as expected)

Re: Partial reload Script (Not working as expected)

Re: Partial reload Script (Not working as expected)

Re: Partial reload Script (Not working as expected)