Incremental load

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

Hi all,

I want to insert incremental load in the given script insert and updates only.

this is the data layer ..can any one help me with this script as we are using the subroutine to fetch data from the database?

//------------------------------------------------------------------------------------------------------------------------

// FACT (Specific part of load script used for loading data, like for instance the creation of a dimension/fact table)

//------------------------------------------------------------------------------------------------------------------------

SUB Fact_ContractLines

CALL LogMessage ('Fact: Extracting Fact Lines (Contract, Material and Equipment Fact Data)')

ContractLines:

LOAD *;

SQL SELECT

TRIM(LEADING '0' FROM COALESCE(Sold2SCBP.PARTNER_FK_SAPACCOUNT_ID,Sold2SCHBP.PARTNER_FK_SAPACCOUNT_ID)) AS SoldToCustomer_Id,

TRIM(LEADING '0' FROM COALESCE(Ship2SCBP.PARTNER_FK_SAPACCOUNT_ID,Ship2SCHBP.PARTNER_FK_SAPACCOUNT_ID)) AS ContractShipToCustomer_Id,

TRIM(LEADING '0' FROM COALESCE(Ship2SCBP.PARTNER_FK_SAPACCOUNT_ID,Ship2SCHBP.PARTNER_FK_SAPACCOUNT_ID,E.Ship_To_Business_Partner_Code)) AS ShipToCustomer_Id,

TRIM(LEADING '0' FROM SC.CONTRACT_NUMBER) AS Contract_Id,

SC.START_DATE as ContractStartDate,

SC.END_DATE as ContractEndDate,

CAST(SC.SALES_AREA AS CHAR(4)) as SalesOrg,

SC.SALES_GROUP as SalesGroup,

SC.SALES_AREA as SalesArea_Id,

SC.PURCHASE_ORDER as CustPONumber_Id,

TRIM(LEADING '0' FROM CL.CONTRACT_LINE_ITEM_NUMBER) AS ContractLine_Id,

CL.LINE_ITEM as ContractLine,

CL.DESCRIPTION as ContractLineDescription,

CL.START_DATE AS ContractLineStartDate,

CL.END_DATE AS ContractLineEndDate,

CL.PROFIT_CENTER AS ProfitCenter_Id,

CL.MATERIAL_ID as AgreementMaterial,

CL.NET_VALUE as ContractLineNetValue,

CL.NET_PRICE as ContractLineNetPrice,

CL.CURRENCY_CODE as ContractCurrency,

CASE

WHEN CL.START_DATE > CURRENT_DATE THEN 'Pending'

WHEN CL.START_DATE<=CURRENT_DATE AND CL.END_DATE>=CURRENT_DATE THEN 'Active'

WHEN CL.END_DATE<CURRENT_DATE THEN 'Expired'

ELSE 'Error'

END as ContractStatus,

TRIM(LEADING '0' FROM E.Equipment_Code) AS Equipment_Id,

E.Technical_Start_Date AS EquipmentStartDate,

E.Equipment_Category_Code as EquipmentCategoryCode,

TRIM(LEADING '0' FROM E.Serial_Number) as SerialNumber,

TRIM(LEADING '0' FROM E.Hardware_Material_Code) as Hardware_Material_Code,

E.Hardware_Serial_Number as Hardware_Serial_Number,

E.Hardware_Version as Hardware_Version,

TRIM(LEADING '0' FROM E.Product_Code) as SystemCode,

M.Description as SystemCodeDescription,

TRIM(LEADING '0' FROM CL.CONTRACT_LINE_ITEM_NUMBER) || '|' || TRIM(LEADING '0' FROM CIP.EQUIPMENT_ID) AS Upgrade_Id,

TRIM(LEADING '0' FROM E.Configurable_Material_Code) AS CommercialMaterial,

CnfM.Description AS CommercialMaterialDescription,

CnfM.End_Of_Life_Material AS Material_EOLDate,

CASE WHEN CnfM.End_Of_Life_Material IS NULL or CnfM.End_Of_Life_Material > CURRENT_DATE THEN 'N' ELSE 'Y' END AS EOLReached,

CnfM.SLCP_Material_Identifier_Code AS SLCPMaterialIdentifier,

CASE

WHEN SLCPMaterialIdentifier IS NULL or SLCPMaterialIdentifier in ('U','N') THEN 'Non-SLCP'

WHEN SLCPMaterialIdentifier in ('B','H','S') THEN 'SLCP'

ELSE NULL

END AS SLCPType,

CASE

WHEN SLCPMaterialIdentifier IS NULL or SLCPMaterialIdentifier in ('U','N') THEN 0

WHEN SLCPMaterialIdentifier in ('B','H','S') THEN 1

ELSE NULL

END AS isSLCP,

CASE

WHEN SLCPMaterialIdentifier IS NULL or SLCPMaterialIdentifier in ('U','N') THEN 1

WHEN SLCPMaterialIdentifier in ('B','H','S') THEN 0

ELSE NULL

END AS isNonSLCP,

E.SLCP_SW_Version AS SLCP_SWVersion,

E.SLCP_Changes_Performed AS SLCP_ChangesPerformed,

E.Remaining_SLCP_Changes AS SLCP_Changes,

SMAInst.SUPPORT_LEVEL_FORMATTED AS NumberOfInstallationsContract,

CASE

WHEN CL.MATERIAL_ID in ($(PCMSPortfolio)) THEN 1

ELSE 0

END AS isPCMS,

CASE

WHEN CL.MATERIAL_ID in ($(HIPortfolio)) THEN 1

ELSE 0

END

AS isHI,

CASE

WHEN M.Is_SMA_Upgrade_Eligible = 'Y' THEN 1 ELSE 0

END AS isSMA_Upgrade_Eligible,

CL.BASE_CONTRACT_ID as Base_Contract_ID,

CL.LINE_CATEGORY as Line_Category

FROM P0_IM_DL_GCS_V.C_CSA_CUSTOMER_SERVICE_CONTRACT AS SC

INNER JOIN P0_IM_DL_GCS_V.C_CSA_CUSTOMER_SERVICE_CONTRACT_LINE AS CL ON SC.CONTRACT_NUMBER = CL.CUSTOMERSERVICECONTRACT_NUMBER

INNER JOIN P0_IM_DL_GCS_V.C_CSA_COVERED_INSTALLED_PRODUCT AS CIP ON CL.CONTRACT_LINE_ITEM_NUMBER = CIP.CUSTOMER_SERVICE_CONTRACT_LINE AND CIP.IS_ACTIVE_FLAG='Y'

LEFT JOIN P0_IM_DL_GCS_V.C_CSA_CUSTOMER_SERVICE_CONTRACT_PARTNERS as Sold2SCBP ON CL.CONTRACT_LINE_ITEM_NUMBER = Sold2SCBP.CONTRACT_LINE_ITEM_NUMBER AND Sold2SCBP.FUNCTION_FK_FUNCTION_CODE='AG'

LEFT JOIN P0_IM_DL_GCS_V.C_CSA_CUSTOMER_SERVICE_CONTRACT_PARTNERS as Ship2SCBP ON CL.CONTRACT_LINE_ITEM_NUMBER = Ship2SCBP.CONTRACT_LINE_ITEM_NUMBER AND Ship2SCBP.FUNCTION_FK_FUNCTION_CODE='WE'

LEFT JOIN P0_IM_DL_GCS_V.C_CSA_CUSTOMER_SERVICE_CONTRACT_PARTNERS as Sold2SCHBP ON CL.CUSTOMERSERVICECONTRACT_NUMBER = Sold2SCHBP.CUSTOMERSERVICECONTRACT_FK_NUMBER AND Sold2SCHBP.FUNCTION_FK_FUNCTION_CODE='AG' AND Sold2SCHBP.CUSTOMERSERVICECONTRACTLINE_FK_LINE_ITEM=0

LEFT JOIN P0_IM_DL_GCS_V.C_CSA_CUSTOMER_SERVICE_CONTRACT_PARTNERS as Ship2SCHBP ON CL.CUSTOMERSERVICECONTRACT_NUMBER = Ship2SCHBP.CUSTOMERSERVICECONTRACT_FK_NUMBER AND Ship2SCHBP.FUNCTION_FK_FUNCTION_CODE='WE' AND Ship2SCHBP.CUSTOMERSERVICECONTRACTLINE_FK_LINE_ITEM=0

-- LEFT JOIN P0_IM_DL_GCS_V.C_PROXY_FOR_BUSINESS_PARTNER AS Sold2BP ON Sold2SCBP.PARTNER_FK_SAPACCOUNT_ID = Sold2BP.SAPACCOUNT_ID

-- LEFT JOIN P0_IM_DL_GCS_V.C_PROXY_FOR_BUSINESS_PARTNER AS Ship2BP ON Ship2SCBP.PARTNER_FK_SAPACCOUNT_ID = Ship2BP.SAPACCOUNT_ID

-- LEFT JOIN P0_IM_DL_GCS_V.C_PROXY_FOR_BUSINESS_PARTNER AS Sold2CBP ON Sold2SCHBP.PARTNER_FK_SAPACCOUNT_ID = Sold2CBP.SAPACCOUNT_ID

-- LEFT JOIN P0_IM_DL_GCS_V.C_PROXY_FOR_BUSINESS_PARTNER AS Ship2CBP ON Ship2SCHBP.PARTNER_FK_SAPACCOUNT_ID = Ship2CBP.SAPACCOUNT_ID

-- LEFT JOIN P0_IM_DL_GCS_V.C_PIB_PROXY_FOR_CUSTOMER_SALES_AREA AS Sold2SA ON COALESCE(Sold2SCBP.PARTNER_FK_SAPACCOUNT_ID,Sold2SCHBP.PARTNER_FK_SAPACCOUNT_ID) = Sold2SA.CUSTOMER_FK_BUSINESS_SAP_ACCOUNT_ID AND SC.SALES_AREA=Sold2SA.SALES_AREA_ID

FULL OUTER JOIN P0_IM_DL_GCS_V.D_Equipment AS E ON CIP.EQUIPMENT_ID=E.Equipment_Code

LEFT JOIN P0_IM_DL_GCS_V.D_Material AS M ON E.Product_Code_With_Zero=M.Material_Code_With_Zero

LEFT JOIN P0_IM_DL_GCS_V.C_CSA_COVERED_SOFTWARE_SUPPORT AS SMAInst ON CL.CONTRACT_LINE_ITEM_NUMBER = SMAInst.CONTRACT_ID_FK AND SMAInst.CHARACTERISTIC_NAME='SMA_SW_INSTALLATIONS' AND SMAInst.CORE_CHANGE_DATE=(SEL Max(CORE_CHANGE_DATE) FROM P0_IM_DL_GCS_V.C_CSA_COVERED_SOFTWARE_SUPPORT AS TP WHERE TP.CONTRACT_ID_FK=CL.CONTRACT_LINE_ITEM_NUMBER AND TP.CHARACTERISTIC_NAME='SMA_SW_INSTALLATIONS')

LEFT JOIN P0_IM_DL_GCS_V.C_PROXY_FOR_BUSINESS_PARTNER AS EquiShip2BP ON E.Ship_To_Business_Partner_Code = EquiShip2BP.SAPACCOUNT_ID

LEFT JOIN P0_IM_DL_GCS_V.D_Material AS CnfM ON E.Configurable_Material_Code=CnfM.Material_Code

WHERE (CL.MATERIAL_ID in ($(PCMSPortfolio),$(HIPortfolio)) OR M.Is_SMA_Upgrade_Eligible = 'Y') AND COALESCE(Ship2SCBP.PARTNER_FK_SAPACCOUNT_ID,Ship2SCHBP.PARTNER_FK_SAPACCOUNT_ID,E.Ship_To_Business_Partner_Code) IS NOT NULL;

// SQL SELECT *

// FROM "P0_IM_DL_GCS_V"."C_CSA_CUSTOMER_SERVICE_CONTRACT_LINE"

// WHERE "MATERIAL_ID" IN ($(PCMSPortfolio),$(HIPortfolio));

CALL LogMessage ('Fact: Store Fact Lines (' & $(fTableRows(ContractLines)) & ' record(s))')

STORE ContractLines INTO $(vPath.QVD)\FACT.qvd;

DROP TABLE ContractLines;

END SUB

Regards

Manu

Re: Incremental load

rubenmarin — Mon, 10 Sep 2018 09:32:46 GMT

Hi Manu, you will need a field that identifies the last modification timestamp to recover updated rows, check the sample in the help:

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/LoadData/use-QVD-files-incremental-load.htm

Example:

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT Exists(PrimaryKey);

STORE QV_Table INTO File.QVD;

Re: Incremental load

manu1512 — Mon, 10 Sep 2018 09:36:41 GMT

Hi ruben,

where should i include this script.?? in the sub routine script ??

or somewhere else?

Regards

Manu

Re: Incremental load

rubenmarin — Mon, 10 Sep 2018 11:41:08 GMT

Yes, it should be in the sub routine, where the qvd is generated. It will need to be adapted to your script, using the equivalent ModificationTime and Primary Key.

Re: Incremental load

manu1512 — Tue, 11 Sep 2018 07:04:40 GMT

stalwar1‌ would be grateful if you can help me ?

topic Incremental load in QlikView

Incremental load

Re: Incremental load

Re: Incremental load

Re: Incremental load

Re: Incremental load