topic Re: Incremental load in qlik in QlikView

Incremental load in qlik

kishorj1982 — Thu, 12 Jul 2018 05:47:26 GMT

Hi All,

PFA.

i am trying to apply incremental load for all tables mentioned in QVW file.

I am confused because I do not have date column in each table.

Could you please suggest how do I implement all 3 scenario's Insert,Insert Update and insert Update delete for each table.

Thanks

Re: Incremental load in qlik

arvind_patil — Thu, 12 Jul 2018 07:41:07 GMT

Hi Kishor,

Please refer below thread. It May help you.

Incremental Load without Date field

Thanks,

Arvind Patil

Re: Incremental load in qlik

kishorj1982 — Fri, 13 Jul 2018 09:58:37 GMT

Can you please share example for incremental load with date field.

How do I apply incremental load for below table.

Can you please suggest, I m confused which date field do I need to use.Sample table in available in attached file.

DimCustomer:

LOAD CustomerId as %CUSTOMER_KEY,

CustomerClerkId,

CustomerClerkName,

CustomerCompanyId,

CustomerCompanyName,

CustomerCode as [Customer No],

CustomerName as [Customer Name],

CustomerName as [Customer],

CustomerCustTypeId,

CustomerCustTypeCode,

CustomerCustTypeName as [Customer Type Name],

if(CustomerCustTypeName='Enterprise','Yritys',

if(CustomerCustTypeName='Private','Yksityinen',CustomerCustTypeName

)) as [FI Customer Type Name],

CustomerStatusId,

CustomerStatusCode,

CustomerStatusName,

CustomerStartDate,

CustomerEndDate,

CustomerSortName,

CustomerNTUser,

CustomerRemtypeId,

CustomerRemtypeCode,

CustomerRemtypeName,

CustomerSegmentationId,

CustomerSegmentationCode,

CustomerSegmentationName,

CustomerContactCentreId,

CustomerContactCentreCode,

CustomerContactCentreName,

CustomerSearchMainPhoneNo as [Phone Number],

CustomerLanguageId,

CustomerLanguageCode,

CustomerLanguageName,

CustomerLoyaltyId,

CustomerLoyaltyCode,

CustomerLoyaltyName,

CustomerInfoId,

CustomerInfoCode,

CustomerInfoName,

CustomerPhonetic,

CustomerSequpd,

CustomerNoremitttoDate,

CustomerBankAccno,

CustomerPrinttargetId,

CustomerPrinttargetCode,

CustomerPrinttargetName,

CustomerDimAccId,

CustomerDimAccCode,

CustomerDimAccName,

CustomerDirtyflag,

CustomerPrinttargetIdCopy,

CustomerUserDate,

CustomerMeterreadMethodId,

CustomerMeterreadMethodCode,

CustomerMeterreadMethodName,

CustomerBankCode,

CustomerInvoiceconfId,

CustomerInvconfId,

CustomerIsupdating,

CustomerWebPwd,

CustomerInvMvcardInfo,

CustomerInvMvcardAuto,

CustomerBranchId,

CustomerBranchCode,

CustomerBranchName,

CustomerFromCurrencyId,

CustomerFromCurrencyCode,

CustomerFromCurrencyName,

CustomerManualZ04aTreatment,

CustomerTennantExemption,

CustomerEANCode,

CustomerAccountTypeId,

CustomerAccounttypeCode,

CustomerAccounttypeName,

CustomerOrgNo,

CustomerOldName,

CustomerGeneralCode1,

CustomerGeneralCode2,

CustomerGeneralCode3,

CustomerVipId,

CustomerVipCode,

CustomerVipName,

CustomerDCCreditCodeId,

CustomerDCChainId,

CustomerChainlock,

CustomerCreditCodeCalcCate,

CustomerIsMarketCustomerId,

CustomerIsMarketCustomerName,

CustomerInvoiceSegmentId,

CustomerInvoiceSegmentCode,

CustomerInvoiceSegmentName,

CustomerRemtypeInvId,

CustomerRemtypeInvCode,

CustomerRemtypeInvName,

CustomerNoRemittToDateInv,

CustomerInvFeeRuleId,

CustomerInvFeeRuleCode,

CustomerInvFeeRuleName,

CustomerEbbicId,

CustomerIban,

CustomerImportanceId,

CustomerImportanceCode,

CustomerImportanceName,

CustomerE2BReceiverId,

CustomerSBCheck,

CustomerLegalstatusId,

CustomerLegalstatusCode,

CustomerLegalstatusName as CustomerLegalstatusName,

CustomerInternalId,

CustomerInternalCode,

CustomerInternalName,

CustomerUseEmailOutputType,

CustomerExtendedMeterValues,

CustomerCustSubTypeId,

CustomerCustSubTypeCode,

CustomerCustSubTypeName,

CustomerMainAddress,

CustomerIsDeletedInSourceSystem,

CustomerMainLoadDate

//CustomerHashKey

FROM

DimCustomer.QVD

(qvd);

Thanks

Re: Incremental load in qlik

amit_saini — Fri, 13 Jul 2018 12:03:13 GMT

Try something if you have Primary Key:

EDID4:

LOAD MANDT,

COUNTER,

SEGNUM,

HLEVEL,

DOCNUM,

SEGNAM,

PSGNUM,

DTINT2,

SDATA

FROM

(qvd);

Last_Updated_SEGNUM:

Load max(SEGNUM) as MaxSEGNUM

Resident EDID4;

Let vLastUpdatedSEGNUM =peek('MaxSEGNUM',0,'Last_Updated_SEGNUM');

NoConcatenate

Incremental:

Load

SQL Select * from EDID4

Where SEGNUM > $(vLastUpdatedSEGNUM);

Concatenate (Incremental)

LOAD MANDT,

COUNTER,

SEGNUM,

HLEVEL,

DOCNUM,

SEGNAM,

PSGNUM,

DTINT2,

SDATA

Resident EDID4

Where not Exists (SEGNUM);

Inner Join (Incremental)

Load

SEGNUM;

SQL Select SEGNUM from EDID4

;

STORE * FROM Incremental INTO D:\qvprod\qvd\SAP\KTX_100\Incremental.QVD;

DROP Tables Last_Updated_SEGNUM, EDID4;

Thanks,