topic Re: Need your help to Automate this script to Reload data based on user request in QlikView

Need your help to Automate this script to Reload data based on user request

Tue, 07 Apr 2015 06:12:22 GMT

Hi Guys,

I am trying for below and so written below script.

There is variable (vFile) to end users to select the months (12,18 24)
Reload Button so that after selecting vFile they should click on Reload button to reload data as they want
Below script I have written to avoid 2nd time reload if any user has loaded the data on same day
By-default, we load only 6 months data and publish to server

Please someone can review the script and tell me why IF ELSEIF condition is not working when I try to reload from application end.

//This is the Script to Generate LOAD_ON_REQUEST_CONFIG_DATA.QVD file

LET vNow=Now();

LOAD_ON_REQUEST_CONFIG_DATA:

LOAD * INLINE [

RunDateTime, NumberOfMonths, LoadFlag

$(vNow), 12, 0

$(vNow), 18, 0

$(vNow), 24, 0

];

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

DROP TABLE LOAD_ON_REQUEST_CONFIG_DATA;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//This File Holds 24 Months of Data into fact table FACT_DATA

BINARY C:\Model_24_MONTHS.qvw;

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

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

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='YYYY-MM-DD hh: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';

//Configuration Table. LoadFlag 1 means data loaded and 0 means not loaded

LOAD_ON_REQUEST_CONFIG:

LOAD

RunDateTime,

NumberOfMonths,

LoadFlag

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

// $(vFile) is Input Variable

//Load 12 months data on request (When user select $(vFile)=12 and click on Reload)

IF $(vFile)=12 AND NumberOfMonths=12 AND LoadFlag=0 THEN

LOAD_ON_REQUEST_CONFIG_DATA:

NOCONCATENATE LOAD

RunDateTime,

NumberOfMonths,

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

RESIDENT LOAD_ON_REQUEST_CONFIG;

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

DROP TABLE LOAD_ON_REQUEST_CONFIG_DATA;

FACT_DATA_1:

NOCONCATENATE LOAD *

RESIDENT FACT_DATA

WHERE _DateKey>=20140407;

DROP TABLE FACT_DATA;

//Load 18 months data on request (When user select $(vFile)=18 and click on Reload)

ELSEIF $(vFile)=18 AND NumberOfMonths=18 AND LoadFlag=0 THEN

LOAD_ON_REQUEST_CONFIG_DATA:

NOCONCATENATE LOAD

RunDateTime,

NumberOfMonths,

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

RESIDENT LOAD_ON_REQUEST_CONFIG;

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

DROP TABLE LOAD_ON_REQUEST_CONFIG_DATA;

FACT_DATA_1:

NOCONCATENATE LOAD *

RESIDENT FACT_DATA

WHERE _DateKey>=20131006;

DROP TABLE FACT_DATA;

//Load 24 months data on request (When user select $(vFile)=24 and click on Reload)

ELSEIF $(vFile)=24 AND NumberOfMonths=24 AND LoadFlag=0 THEN

LOAD_ON_REQUEST_CONFIG_DATA:

NOCONCATENATE LOAD

RunDateTime,

NumberOfMonths,

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

RESIDENT LOAD_ON_REQUEST_CONFIG;

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

DROP TABLE LOAD_ON_REQUEST_CONFIG_DATA;

FACT_DATA_1:

NOCONCATENATE LOAD *

RESIDENT FACT_DATA;

DROP TABLE FACT_DATA;

ELSEIF ($(vFile)=12 OR $(vFile)=18 OR $(vFile)=24) AND LoadFlag=1 THEN

LOAD

MsgBox ($(vFile)&' Months Data has been already loaded by other user and available for all') autogenerate 1;

ELSE

FACT_DATA_1:

//Load 6 months Data bydefault

NOCONCATENATE LOAD *

RESIDENT FACT_DATA

WHERE _DateKey>=20141006;

DROP TABLE FACT_DATA;

END IF;

DROP TABLE LOAD_ON_REQUEST_CONFIG;

Re: Need your help to Automate this script to Reload data based on user request

pokassov — Tue, 07 Apr 2015 06:21:04 GMT

Hi!

You have to "extract" values from table to variable:

Let vNumberOfMonths=Peek('NumberOfMonths',0,'LOAD_ON_REQUEST_CONFIG');

Let vLoadFlag=Peek('LoadFlag',0,'LOAD_ON_REQUEST_CONFIG');

and then you can use them without $()

if vNumberOfMonths=24 and ...

Sergey.

Re: Need your help to Automate this script to Reload data based on user request

Tue, 07 Apr 2015 09:19:57 GMT

Hi Sergey,

As you suggested, I have corrected it and now it is working but I am facing couple of issues:

When Initially I reload, I want to reload 6 months data but in below script it is executing block IF $(vFile)=12 AND v12NumberOfMonths=12 AND v12LoadFlag=0 THEN. I was expecting to execute ELSE condition. The reason it is executing 1st block because $(vFile) default value is coming as 12.
How can I remove default value of Input variable $(vFile)? This variable is defined as predefined value which is having 3 values 12, 18 and 24. Default it is showing 12.

Kindly Help it in resolving it?

//This File Holds 24 Months of Data into fact table FACT_DATA

BINARY C:\Model_24_MONTHS.qvw;

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

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

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='YYYY-MM-DD hh: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';

//Configuration Table. LoadFlag 1 means data loaded and 0 means not loaded

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;

LOAD_ON_REQUEST_CONFIG_24:

NOCONCATENATE LOAD *

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

WHERE NumberOfMonths=24;

LET v24NumberOfMonths=PEEK('NumberOfMonths',0,'LOAD_ON_REQUEST_CONFIG_24');

LET v24LoadFlag=PEEK('LoadFlag',0,'LOAD_ON_REQUEST_CONFIG_24');

DROP TABLE LOAD_ON_REQUEST_CONFIG_24;

// $(vFile) is Input Variable

//Load 12 months data on request (When user select $(vFile)=12 and click on Reload)

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

LOAD_ON_REQUEST_CONFIG_DATA:

NOCONCATENATE LOAD

RunDateTime,

NumberOfMonths,

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

RESIDENT LOAD_ON_REQUEST_CONFIG;

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

DROP TABLE LOAD_ON_REQUEST_CONFIG_DATA;

FACT_DATA_1:

NOCONCATENATE LOAD *

RESIDENT FACT_DATA

WHERE _DateKey>=20140407;

DROP TABLE FACT_DATA;

//Load 18 months data on request (When user select $(vFile)=18 and click on Reload)

ELSEIF $(vFile)=18 AND v12NumberOfMonths=18 AND v12LoadFlag=0 THEN

LOAD_ON_REQUEST_CONFIG_DATA:

NOCONCATENATE LOAD

RunDateTime,

NumberOfMonths,

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

RESIDENT LOAD_ON_REQUEST_CONFIG;

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

DROP TABLE LOAD_ON_REQUEST_CONFIG_DATA;

FACT_DATA_1:

NOCONCATENATE LOAD *

RESIDENT FACT_DATA

WHERE _DateKey>=20131006;

DROP TABLE FACT_DATA;

//Load 24 months data on request (When user select $(vFile)=24 and click on Reload)

ELSEIF $(vFile)=24 AND v12NumberOfMonths=24 AND v12LoadFlag=0 THEN

LOAD_ON_REQUEST_CONFIG_DATA:

NOCONCATENATE LOAD

RunDateTime,

NumberOfMonths,

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

RESIDENT LOAD_ON_REQUEST_CONFIG;

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

DROP TABLE LOAD_ON_REQUEST_CONFIG_DATA;

FACT_DATA_1:

NOCONCATENATE LOAD *

RESIDENT FACT_DATA;

DROP TABLE FACT_DATA;

ELSEIF ($(vFile)=12 AND v12LoadFlag=1) OR ($(vFile)=18 AND v18LoadFlag=1) OR ($(vFile)=24 AND v24LoadFlag=1) THEN

LOAD

MsgBox ($(vFile)&' Months Data has been already loaded by other user and available for all') autogenerate 1;

ELSE

FACT_DATA_1:

//Load 6 months Data bydefault

NOCONCATENATE LOAD *

RESIDENT FACT_DATA

WHERE _DateKey>=20141006;

DROP TABLE FACT_DATA;

END IF;

DROP TABLE LOAD_ON_REQUEST_CONFIG;

Re: Need your help to Automate this script to Reload data based on user request

pokassov — Tue, 07 Apr 2015 09:23:44 GMT

I fink you have to do the same thing

IF vFile=12 AND v12NumberOfMonths=12 AND v12LoadFlag=0 THEN

Re: Need your help to Automate this script to Reload data based on user request

Tue, 07 Apr 2015 10:15:27 GMT

Same result. It is executing the block IF vFile=12 AND v12NumberOfMonths=12 AND v12LoadFlag=0 THEN 1st

Re: Need your help to Automate this script to Reload data based on user request

pokassov — Tue, 07 Apr 2015 10:26:42 GMT

Ok. I got it.

You can use another variable. For example vUserReload

Add 2 actions before reload and after reload

in your script you can write these lines before using vFile in conditions.

If vUserReload=0 then

Set vFile=24;

end if;

Re: Need your help to Automate this script to Reload data based on user request

Tue, 07 Apr 2015 10:42:08 GMT

But I did not get it. What it is going to make difference?

When initial load and publish I am doing it should load only 6 months data means should execute this block:

ELSE

FACT_DATA_1:

//Load 6 months Data bydefault

NOCONCATENATE LOAD *

RESIDENT FACT_DATA

WHERE _DateKey>=20141006;

DROP TABLE FACT_DATA;

and if some user trying to reload from section access then it should execute and reload based on user selection

Re: Need your help to Automate this script to Reload data based on user request

pokassov — Tue, 07 Apr 2015 11:04:20 GMT

I'm sorry, It's my mistake.

If vUserReload=0 then

Set vFile=40; //any numbers except 12, 18, 24

end if;