Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I am trying for below and so written below script.
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;
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.
Hi Sergey,
As you suggested, I have corrected it and now it is working but I am facing couple of issues:
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;
I fink you have to do the same thing
IF vFile=12 AND v12NumberOfMonths=12 AND v12LoadFlag=0 THEN
Same result. It is executing the block IF vFile=12 AND v12NumberOfMonths=12 AND v12LoadFlag=0 THEN 1st
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;
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
I'm sorry, It's my mistake.
If vUserReload=0 then
Set vFile=40; //any numbers except 12, 18, 24
end if;