Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Partner
Partner

Why is this QVD Load not optimized?

I am attempting to improve the performance of a particular load script by ensuring QVD Loads are optimised. 

Load Script as follows:

The following code ensures optimized load.

LET OrgId = 81;

UNQUALIFY;

OrgLoad:

LOAD * INLINE

[OrgId

$(OrgId) ];

TempAP:

LOAD *

FROM

..\2_QVD\AP_INVOICES_ALL.qvd (qvd)

WHERE EXISTS (OrgId);

The following code does not use optimized load when naming the fields individually.  There are no calculations or other tranformations that would cause this to be an unoptimized load as far as I understand.

LET OrgId = 81;

UNQUALIFY;

OrgLoad:

LOAD * INLINE

[OrgId

$(OrgId) ];

TempAP:

LOAD

    %AP_INVOICES_KEY,

    %AP_CALENDAR_KEY,

   APInv.InvoiceId,

    APInv.InvoiceNum, 

    APInv.Currency,

    APInv.InvoiceDate,

    APInv.Source,

    APInv.InvoiceType,

    APInv.Description,

    APInv.TermsDate,

    APInv.PaymentMethod,

    APInv.PayGroup,

    APInv.PaymentStatusFlag,

    APInv.CreationDate,

    APInv.XMLReceivedDate,

    APInv.CreatedBy,

    APInv.BaseAmount,

   APInv.GoodsReceivedDate,

    APInv.InvoiceReceivedDate,

    APInv.ApprovedAmount,

    APInv.VoucherNum,

    APInv.DocSequence,

    APInv.DFF1,

    APInv.DFF2,

    APInv.DFF3,

    APInv.DFF4,

    APInv.DFF5,

    APInv.InDispute,  /*CM010500*/

   APInv.InDisputeReason,  /* Ctes 2017/04 Added*/

    APInv.GLDate,

    APInv.CancelledDate,

    APInv.ApprovalStatus,

    APInv.EnteredBy,

    APInv.VendorNumber,

    APInv.VendorName,

    APInv.VendorSmallBusinessFlag,    /*REL2015/04*/

    APInv.VendorMinorityType,         /*REL2015/04*/

    APInv.VendorSitePaymentFax,       /*REL2015/04*/

    APInv.VendorSitePostCode,         /*REL2015/04*/

    APInv.VendorSitePaymentPriority, /*REL2015/06*/

     APInv.VendorSiteSensitiveData,  /*RELEASE 2018/01 */

    APInv.VendorSiteCode,

    APInv.VendorSiteTaxCode,  //Added REL1304

    APInv.VendorType,         //Added REL1304

    APInv.VendorSitePaymentEmail, /*REL2015-01*/

    APInv.BatchName,

    APInv.BatchDate,              /*RELEASE 2018/01 */

    APInv.OrgShortCode,           /*RELEASE 2018/01 */

    APInv.FutureDatedInvoiceFlag,       /*RELEASE 2018/01 */

    APInv.LastUpdateDate,

    APInv.LastUpdatedBy, /* ADDED 201702 CTES */

    APInv.CancelledAmt, /* ADDED 201702 CTES */

    JOIN_APINV_ORGID, 

    APInv.CertificateNumber,    /*Added Release 2017/03*/

    APInv.CertificateType,        /*Added Release 2017/03*/

    APInv.Comments,                /*Added Release 2017/03*/

    APInv.TaxName,                /*Added Release 2017/03*/

    APInv.BatchCreatedBy,   /*Added in 2017/04 CTES*/

    APInv.BatchCreatedByName ; /*Added in 2017/04 CTES*/

    FROM

..\2_QVD\AP_INVOICES_ALL.qvd (qvd)

WHERE EXISTS (OrgId);

The following simplified load script also results in a non optimized oad

LET OrgId = 81;

UNQUALIFY;

OrgLoad:

LOAD * INLINE

[OrgId

$(OrgId) ];

TempAP:

LOAD

    %AP_INVOICES_KEY

   FROM

..\2_QVD\AP_INVOICES_ALL.qvd (qvd)

WHERE EXISTS (OrgId);

Tags (1)
3 Replies
MVP & Luminary
MVP & Luminary

Re: Why is this QVD Load not optimized?

Maybe it's your unqualify-statement which missed any fields or a wildcard. Therefore try:

UNQUALIFY *;

OrgLoad: LOAD 81 as OrgId autogenerate 1;

TempAP:

LOAD

    %AP_INVOICES_KEY

   FROM

..\2_QVD\AP_INVOICES_ALL.qvd (qvd)

WHERE EXISTS(OrgId);

- Marcus

Partner
Partner

Re: Why is this QVD Load not optimized?

Thanks for your response Marcus.

Good spot there, but unfortunately that doesn't seem to make any difference.

MVP & Luminary
MVP & Luminary

Re: Why is this QVD Load not optimized?

Normally the above should work. Just put this load into a complete new qvw without any other code and try it again. I assume it will work then and this means that any other stuff within your other qvw prevents the optimized loading.

I never noticed such behaviour before but I could imagine that some outside-logics in regard of a load-statement might enforce a processing of the data, for example NULL-variables like NULLASVALUE or BUFFER statements and probably some other rarely used options more.

- Marcus