use sql query and spectrum connection to create incremental qvd with data from 1st jan 2019.
SELECT B.DetailID ,IF(K.country = "AU","AU",IF(K.territory = "EU" ,K.country,IF(K.territory = "GB","GB",K.territory))) AS LTSTerritory ,IF(B.orderType = "b2c" AND K.channel = "Retail","SMO", IF(B.orderType = "b2c","Direct","Retail")) AS Market ,IF(B.accumulatorType in ("RETAIL_RETURNED","RETURNED"),"Return",IF(B.accumulatorType = "REFUNDED","Refunded","Sale")) AS TransactionType ,B.Dated ,B.OrderDate ,B.PayType ,IFNULL(PO.payTypeName,IF(B.payType="CARD",CONCAT(B.payType,"-UNKNOWN"),B.payType)) AS PayTypeName ,IF(B.SubPayType="" OR B.subPayType IS NULL,IF(B.PayType="GIFTVOUCHER","EX",B.payType) ,B.subPayType) AS subPayType
,IF(PO.schemeName="" OR PO.schemeName IS NULL,IF(B.payType="CARD",CONCAT(B.payType,"-UNKNOWN"),B.payType),PO.schemeName) AS SubPayTypeName ,IF(B.accumulatorType in ("REFUNDED","RETAIL_RETURNED","RETURNED"), -B.Value,B.Value) AS Value ,K.Currency ,P.AuthID
FROM client_lts.CORE_AccumulatorsBankingTransactionLog B INNER JOIN client_lts.CORE_AccumulatorsKey K ON B.accumulatorKeyID = K.ID INNER JOIN client_lts.ORDER_Payments P ON B.detailID = P.detailID AND B.orderPaymentID = P.ID LEFT JOIN client_lts.CLIENT_PaymentOptions PO ON B.payType = PO.payType AND B.subPayType = PO.scheme
WHERE B.dated BETWEEN "2018-10-01" AND "2018-10-02" AND B.accumulatorType IN ("BILLED","RETAIL","RETAIL_RETURNED","RETURNED","REFUNDED")
This is sql query.
can any one help how to write a code for incremental qvd.
