Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
kakaderanjit53
Creator III
Creator III

Data Updation in the QVDs

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.

6 Replies
saimahasan
Partner
Partner

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.

sdmech81
Specialist
Specialist

HI,

In place of year filter u pass moth one.

Plss paste the sample script?

Sachin

kakaderanjit53
Creator III
Creator III
Author

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;

sdmech81
Specialist
Specialist

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

kakaderanjit53
Creator III
Creator III
Author

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)

kakaderanjit53
Creator III
Creator III
Author

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.