topic Re: not understanding code in Qlikview in QlikView

not understanding code in Qlikview

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: not understanding code in Qlikview

PrashantSangle — Thu, 30 Jun 2016 10:20:40 GMT

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

;

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

Re: not understanding code in Qlikview

anushree1 — Thu, 30 Jun 2016 10:21:31 GMT

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/

Re: not understanding code in Qlikview

korsikov — Thu, 30 Jun 2016 10:37:08 GMT

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

;

Re: not understanding code in Qlikview

Anonymous — Thu, 30 Jun 2016 10:59:04 GMT

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

Re: not understanding code in Qlikview

PrashantSangle — Thu, 30 Jun 2016 11:08:20 GMT

Hi,

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

Regards

Re: not understanding code in Qlikview

Anonymous — Thu, 30 Jun 2016 11:19:13 GMT

thank-you Max

Re: not understanding code in Qlikview

PrashantSangle — Thu, 30 Jun 2016 11:20:15 GMT

Welcome

Re: not understanding code in Qlikview

Anonymous — Tue, 05 Jul 2016 09:06:31 GMT

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