topic Re: Data Updation in the QVDs in QlikView

Data Updation in the QVDs

kakaderanjit53 — Tue, 14 Feb 2017 07:46:15 GMT

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.

Re: Data Updation in the QVDs

saimahasan — Tue, 14 Feb 2017 08:58:31 GMT

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.

Re: Data Updation in the QVDs

sdmech81 — Tue, 14 Feb 2017 09:07:23 GMT

HI,

In place of year filter u pass moth one.

Plss paste the sample script?

Sachin

Re: Data Updation in the QVDs

kakaderanjit53 — Tue, 14 Feb 2017 10:24:24 GMT

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;

Re: Data Updation in the QVDs

sdmech81 — Tue, 14 Feb 2017 12:02:02 GMT

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

Re: Data Updation in the QVDs

kakaderanjit53 — Wed, 15 Feb 2017 06:43:42 GMT

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)

Re: Data Updation in the QVDs

kakaderanjit53 — Wed, 15 Feb 2017 06:47:46 GMT

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.