Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
the following script (starting from under the dotted line) has stopped loading data into the 'Member_Details.qvd' file so i am trying to understand the script to then find the issue. This is run on the first day of ever month and is supposed to keep history and concatenate last months transactions to the file. I don't need to understand the joins or tables. I am unfamiliar with a few areas within the script so any assistance in understanding the following questions would be appreciated.
Cheers.
.........................................................
//Set qvd fully qualified name
Let varFileName_UAT= '$(varDirectory_UAT)'& 'Member_Details.qvd';
//Check if qvd already exists
if isnull(filetime('$(varFileName)')) then
//file doesnt exists - save first snapshot
MonthlySnapshots_Detail:
Load
DateUpdated,
AccountName,
AccountNumber;
SQL
select getdate() as DateUpdated,
fa.name as AccountName,
fa.accountnumber as AccountNumber
from filteredaccount fa join
membership m on fa.accountid = m.snap_accountid
and m.from <= convert(datetime,convert(char(12),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),103),103)+1
;
//Store in QVD File
STORE MonthlySnapshots_Detail INTO $(varFileName_UAT);
//else//
MonthlySnapshots_Detail:
Load
DateUpdated,
AccountName,
AccountNumber;
SQL
select distinct
getdate() as DateUpdated,
fa.name as AccountName,
fa.accountnumber as AccountNumber
from product
left join subproduct on subproduct. productid = product.productid
where m.validfrom <= convert(datetime,convert(char(12),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),103),103) +1 ;
Concatenate (MonthlySnapshots_Detail)
Load
DateUpdated,
FiscalYear,
Month,
AccountName,
AccountNumber
from $(varFileName) (qvd)
where SnapshotDate <=date($(varMonthEndNum),'DD/MM/YYYY');
//Store in QVD File
STORE MonthlySnapshot INTO $(varFileName_UAT);
end if
Hi,
above code is incremental qvd generator code
1: below code represent presending load. It will load DatUpdated, AccountName and AccountNumeber from SQL query output.
Store command work when below enitre script executed. i.e. both Load and SQL
Load
DateUpdated,
AccountName,
AccountNumber;
SQL
select getdate() as DateUpdated,
fa.name as AccountName,
fa.accountnumber as AccountNumber
from filteredaccount fa join
membership m on fa.accountid = m.snap_accountid
and m.from <= convert(datetime,convert(char(12),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),103),103)+1
;
2:
where m.validfrom <= convert(datetime,convert(char(12),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),103),103) +1 ;
What you dont understand in above where clause
DateAdd() is SQL server function for add date
Convert() is use to convert datatype of column,
DateDiff() is use to calculate date difference.
103 is used to format date i.e. DD/MM/YYYY
Regards
Hi,
Store command is used to save the data extracted from the source into the qvd file.
I suppose the error in loading is due to error in the store command.
STORE MonthlySnapshots_Detail INTO $(varFileName_UAT);
The above store command is referring MonthlySnapshots_Detail table to be stored into a file which is defined in the varFileName_UAT variable.
Let varFileName_UAT= '$(varDirectory_UAT)'& 'Member_Details.qvd';
In the script I do not see the varDirectory_UAT defined so please write the below code at the start of the script
Let varDirectory_UAT=<<Give the file location where the the qvd must be saved>>
Eg: Let varDirectory_UAT= C:/Mydocs/Data/
that you want to understand?
MonthlySnapshots_Detail: // there is set table name and then load in this table 3 lines.
Load
DateUpdated, // First line
AccountName, //second
AccountNumber; //3-th line
/// We obtain data from the SQL request
SQL // all this as described below is performed on the SQL server side
select getdate() as DateUpdated,
fa.name as AccountName,
fa.accountnumber as AccountNumber
from filteredaccount fa join
membership m on fa.accountid = m.snap_accountid
and m.from <= convert(datetime,convert(char(12),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),103),103)+1
;
thanks Max. got it. it runs the sql and load the results in both the table and the Member_Details.qvd. Next two questions please........
//else//
MonthlySnapshots_Detail:
Load
DateUpdated,
AccountName,
AccountNumber;
SQL
select distinct
getdate() as DateUpdated,
fa.name as AccountName,
fa.accountnumber as AccountNumber
from product
left join subproduct on subproduct. productid = product.productid
where m.validfrom <= convert(datetime,convert(char(12),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),103),103) +1 ;
Concatenate (MonthlySnapshots_Detail)
Load
DateUpdated,
FiscalYear,
Month,
AccountName,
AccountNumber
from $(varFileName) (qvd)
where SnapshotDate <=date($(varMonthEndNum),'DD/MM/YYYY');
//Store in QVD File
STORE MonthlySnapshot INTO $(varFileName_UAT);
end if
Hi,
In else criteria if file exist then New records fetch from database and concatenated with existing file records.
Regards
thank-you Max
Welcome
Hi Max,
do you have any examples of code whereby a file is generated to store into a qvd file each month and then the data from this file adds to a qvw table each month? I have been shown one but I need something a little more straight forward.
Cheers,
Laura