not understanding code in Qlikview

[Anonymous]

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. 

• i get the script begins at the first SQL highlighted in blue (which links to the database specified in the main table).  My understanding is from here it then loads the data into the MonthlySnapshots_Detail: temp table (highlighted in pink).  I don't understand when the "STORE MonthlySnapshots_Detail INTO $(varFileName_UAT);  comes into play?  Does this come into play at the end of the first sql or after the load?

• I also don't understand what "where m.validfrom <= convert(datetime,convert(char(12),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),103),103) +1 ;" means?

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

[PrashantSangle]

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

[anushree1]

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/

[korsikov]

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

  ;

[Anonymous]

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........ 

• I don't understand the relevance after the "Else".  i thought one would use "else" if the first criteria was not met but the sql after "else" seems to be identical to the first one?

• Second question please-  I understand that the second SQL runs and then loads any results-data into MonthlySnapshots_Detail: table.  Then what happens ?

//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

[PrashantSangle]

Hi,

In else criteria if file exist then New records fetch from database and concatenated with existing file records.

Regards

[Anonymous]

thank-you Max

[PrashantSangle]

Welcome

[Anonymous]

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