Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik Community,
I have been done the some changes in the SQL_DB, and Now I have to update the already created
QVDs for retrieving the updated changed data-details.
for E.g. I have QVD file i.e. LOGIN_DATA_2016-2017 which contains fiscal year data, but now I have to update the same QVD file
with new updated database changes and now The data need to be pulled one month at a time instead of a "year"
How can do it?
Needs your important reply.
Hi Ranjit,
If you want to fetch the updated data from the DB so can't you simply refresh your load stm in QV??
It will fetch the latest updated data. Also for one month data you can put the condition where Month = 'Your month for which is to be pulled.'
let me know if you were asking something else.
HI,
In place of year filter u pass moth one.
Plss paste the sample script?
Sachin
Hi Sachin,
PFB Existing script used for the data pulling from SQL server for the Fiscal_year, and now only data load for a month rectify wherever necessary;
LET vQvd_Path = 'lib://D (#############)\QVDs\'&'$(vPath)'&'\Transactions';
IF($(vDays)=0 and ('$(vFrom_Date)'='NONE' or '$(vTo_Date)'='NONE')) THEN
//SINCE BOTH ARE NOT PASSED SCRIPT EXIT WITH ERROR
TRACE <<< INPUTS vDays,vFrom_Date and vTo_Date ARE WRONG. PLEASE ENTER ONLY ONE VALID INPUT;
XET WILL_EXIT; //IS STATEMENT IS TO EXIT THE SCRIPT. PLEASE READ ABOVE LINE FOR DETAILS
Exit Script;
END IF
IF($(vDays)<>0 ) THEN
LET vTo_Date = Date(Today(),'YYYYMMDD');
LET vFrom_Date = Date(Today()-$(vDays),'YYYYMMDD');
ELSE
LET vTo_Date = $(vTo_Date);
LET vFrom_Date = $(vFrom_Date);
END IF
// connecting to DB for Store Proc Call
LIB CONNECT TO 'db#####.com (ksec_ks7714)';
Incremental_data:
Load *;
SQL exec qlik_ods_data $(vSP_Flag),$(vFrom_Date),$(vTo_Date);
// Creating a temp tab to hold fiscal year summary data
FinYear_Data:
Load fiscal_year,Min(trade_date) as Min_Date,Max(trade_date) as Max_Date
Resident Incremental_data
Group by fiscal_year order by fiscal_year;
// Looping over the FinYear_Data table to replace data for the prescribed period across qvds
For vLoop = 0 to NoOfRows('FinYear_Data')-1
LET vQvd_FinYear = Peek('fiscal_year',$(vLoop),'FinYear_Data');
LET vMin_Date = Peek('Min_Date',$(vLoop),'FinYear_Data');
LET vQvd_Name = '$(vSP_Flag)'&'_'&'$(vQvd_FinYear)'&'.qvd';
//If QVD is already existing
IF(not isnull(QvdCreateTime('$(vQvd_Path)\$(vQvd_Name)'))) then
//Load existing data prior to min_date
NoConcatenate
QVD_Data:
Load *
from [$(vQvd_Path)\$(vQvd_Name)](qvd)
where trade_date < $(vMin_Date);
//Append the incremental data from min_date to max_date (fiscal year to date)
Concatenate(QVD_Data)
Load *
Resident Incremental_data
where fiscal_year='$(vQvd_FinYear)';
//If QVD is not existing (new FY started or 1st time migration)
ELSE
NoConcatenate
QVD_Data:
Load *
Resident Incremental_data
where fiscal_year='$(vQvd_FinYear)';
END IF
//create or repalce the QVD with revised data
Store QVD_Data into [$(vQvd_Path)\$(vQvd_Name)](qvd);
drop table QVD_Data;
Next vLoop;
Drop Tables FinYear_Data,Incremental_data;
HI,
This looks like daily incremental load..
If u r looking fr 1month data extract,load tht QVD which contains full data..Then u can apply filter on the field Trade_date to pull in the 1months data in Qlikview front end.
Sachin
Hi Sachin,
Yes, above script is for the daily incremental load,
Now I am using the following script for Data load by every month instaed of whole year data at one time.
set vdays=0;
set vTo_date=20160430;
set vFrom_date=20160401;
set vSP_Flag='ITMS_ACTIVITY_DATA';
($must include=QVS file path)
Actually I was trying to pull the SQL data into the already created QVD file because there is some changes in the logic of SP flag and sql tables.,Now I am loading the Monthwise data in the already created QVD file., i.e. appending the monthwise data in the QVD file.