topic script help in QlikView

script help

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

hi all,

anyone please help me in understanding below script including loops

Contact_SQL:

SQL SELECT

Id,

AccountId,

LastName,

FirstName,

Salutation,

OtherStreet,

OtherCity,

OtherState,

OtherPostalCode,

OtherCountry,

MailingStreet,

MailingCity,

MailingState,

MailingPostalCode,

MailingCountry,

Phone,

Fax,

MobilePhone,

HomePhone,

OtherPhone,

AssistantPhone,

ReportsToId,

Email,

Department,

AssistantName,

LeadSource,

Birthdate,

Description,

OwnerId,

CreatedDate,

CreatedById,

LastModifiedDate,

LastModifiedById,

SystemModstamp,

LastCURequestDate,

LastCUUpdateDate

FROM Contact

WHERE LastModifiedDate >= $(vLastExecTime) and LastModifiedDate < $(vExecTime);

// For the 1st reload, this section will be skipped.

if Not $(FirstReload) and not isnull(QvdCreateTime('$(vQVDPath)Contact_SQL.QVD'))then

Load *

FROM $(vQVDPath)Contact_SQL.qvd (qvd)

WHERE NOT EXISTS(Id);

end if

// If Opportunity table exists then proceed to the next step

if NoOfRows('Contact_SQL') > 0 then

STORE Contact_SQL INTO $(vQVDPath)Contact_SQL.qvd;

Contact:

Load

Id as [Contact Id],

AccountId as AccountId,

LastName as [Contact First Name],

FirstName as [Contact Last Name],

Salutation as [Contact Salutation],

Salutation & ' ' & FirstName & ' ' & LastName as [Contact Full Name],

OtherStreet as [Contact Other Street],

OtherCity as [Contact Other City],

OtherState as [Contact Other State],

OtherPostalCode as [Contact Other PostalCode],

OtherCountry as [Contact Other Country],

MailingStreet as [Contact Mailing Street],

MailingCity as [Contact Mailing City],

MailingState as [Contact Mailing State],

MailingPostalCode as [Contact Mailing PostalCode],

MailingCountry as [Contact Mailing Country],

Phone as [Contact Phone],

Fax as [Contact Fax],

MobilePhone as [Contact Phone (Mobile)],

HomePhone as [Contact Phone (Home)],

OtherPhone as [Contact Phone (Other)],

AssistantPhone as [Contact Phone (Assistant)],

Email as [Contact Email],

Department as [Contact Dept],

AssistantName as [Contact Assistant],

Birthdate as [Contact Birthday],

Description as [Contact Description],

OwnerId as [Contact Owner Id],

CreatedDate as [Contact CreatedDate],

CreatedById as [Contact CreatedById],

LastModifiedDate as [Contact LastModifiedDate],

LastModifiedById as [Contact LastModifiedById],

SystemModstamp as [Contact SystemModstamp],

LastCURequestDate as [Contact LastCURequestDate],

LastCUUpdateDate as [Contact LastCUUpdateDate]

RESIDENT Contact_SQL;

DROP TABLE Contact_SQL;

End if

regards

manu

Re: script help

Peter_Cammaert — Mon, 19 Feb 2018 20:04:07 GMT

There are no loops present in this script.

It boils down to something like:

From DBMS, load rows that fall in a certain window (see WHERE clause) into an internal table called Contact_SQL

IF FirstLoad is not set, and a previous copy of this table exists in a QVD file,

THEN

From QVD file, add all rows to the internal table Contact_SQL that don't already exist

END IF

IF Contact_SQL has more than 0 rows

THEN

STORE Contact_SQL into the external QVD file (overwrite if it already exists)

COPY Contact_SQL into internal table Contact

DELETE internal table Contact_SQL

END IF

Best,

Peter

Re: script help

jwjackso — Mon, 19 Feb 2018 20:11:19 GMT

Prior to the section of the script that you posted, I believe you will find the variables vLastExecTime, vExecTime and FirstReload set.

QvdCreateTime(\\Path\QVD_NAME) is a function that returns the timestamp of the QVD_NAME.

In this section, select data between the 2 timestamps, vLastExecTime and vExecTime.

FROM Contact

WHERE LastModifiedDate >= $(vLastExecTime) and LastModifiedDate < $(vExecTime);

In this section, if this is the first time the script is run, there should not be a qvd. If this is not the first time the script is run and there was not data selected during the first run, there should not be a qvd. If this is not the first run and a qvd exist, load the previous selected records from the qvd with the ignoring the records with Id that exists in the Contact_SQL that was just generated. You are basically concatenating the unique records in the qvd to the query above. Looks like you have only the last loaded Id record.

// For the 1st reload, this section will be skipped.

if Not $(FirstReload) and not isnull(QvdCreateTime('$(vQVDPath)Contact_SQL.QVD'))then

Load *

FROM $(vQVDPath)Contact_SQL.qvd (qvd)

WHERE NOT EXISTS(Id);

end if

In this section, if you have records you will create a new qvd or overwrite the existing qvd with the concatenated records.

// If Opportunity table exists then proceed to the next step

if NoOfRows('Contact_SQL') > 0 then

STORE Contact_SQL INTO $(vQVDPath)Contact_SQL.qvd;

In this section, create a new table Contact from Contact_SQL renaming fields and then you drop the original Contact_SQL.

Contact:

Load

Id as [Contact Id],

AccountId as AccountId,

LastName as [Contact First Name],

Re: script help

manu1512 — Mon, 19 Feb 2018 21:16:10 GMT

Thank you peter

Re: script help

manu1512 — Mon, 19 Feb 2018 21:16:35 GMT

Thanks jerry