FOR..NEXT LOOP is skipping record on incremental load
I have run into a bizarre issue during an incremental load scenario.
I have a Sales Order List table and a Sales Order Lines table which is loaded and stored into a QVD daily.
The data set is becoming too big to perform the full loads daily and so I am now changing the code to perform an incremental load to reduce the load time.
I am encountering an issue during the FOR...NEXT loop when reloading the Sales order lines, some of the lines are skipped during the load and I am unable to identify the error in the code.
Overview of test data load
Total Sales Orders in list: 593
TotalSales Orders modified since last reload: 9 (currently using today()-7 just so I get sufficient test data loaded.)
When i run throught the FOR...NEXT loop certain records are being skipped despite the fact that I have verified the record exists. only 5 of the sales orders load.
STEP 1: Load Sales Orders from QVD
//LOAD Sales Order List from the QVD
tSalesList: LOAD SLSalesOrderID AS tSLSalesOrderID, SLSalesOrderNo AS tSLSalesOrderNo, SLSalesOrderStatus AS tSLSalesOrderStatus, SLSalesOrderDate AS tSLSalesOrderDate, SLSalesOrderTimeStamp AS tSLSalesOrderTimeStamp, SLSalesInvoiceDate AS tSLSalesInvoiceDate, SLSalesInvoiceTimestamp AS tSLSalesInvoiceTimestamp, SLCustomerID AS tSLCustomerID, SLSalesInvoiceNo AS tSLSalesInvoiceNo, SLWooCommerceID AS tSLWooCommerceID, SLSalesInvoiceAmount AS tSLSalesInvoiceAmount, SLSalesOrderPaidAmount AS tSLSalesOrderPaidAmount, SLSalesInvoiceDueDate AS tSLSalesInvoiceDueDate, SLSalesOrderShipByDate AS tSLSalesOrderShipByDate, SLSalesOrderCurrency AS tSLSalesOrderCurrency, SLCustomerCurrency AS tSLCustomerCurrency, SLSalesCreditNoteNo AS tSLSalesCreditNoteNo, SLSalesOrderLastUpdated AS tSLSalesOrderLastUpdated, SLSalesQuoteStatus AS tSLSalesQuoteStatus, SLStatusSaleOrder AS tSLStatusSaleOrder, SLStatusSalePicking AS tSLStatusSalePicking, SLStatusSalePayment AS tSLStatusSalePayment, SLSaleTrackingNo AS tSLSaleTrackingNo, SLStatusSalePacking AS tSLStatusSalePacking, SLStatusSaleShipping AS tSLStatusSaleShipping, SLStatusSaleInvoice AS tSLStatusSaleInvoice, SLStatusSaleCreditNote AS tSLStatusSaleCreditNote, SLSaleFulFillStatus AS tSLSaleFulFillStatus, SLSalesOrderType AS tSLSalesOrderType, SLSalesOrderChannel AS tSLSalesOrderChannel, SLSalesOrderExternalID AS tSLSalesOrderExternalID, SLSalesOrderFulfilled AS tSLSalesOrderFulfilled
FROM [C:\Users\Molecule Liquids\Dropbox\Xhype Liquid Co\Business Intelligence\Data Warehouse\QVDs\dl_salesorderlist.qvd] (qvd);
STEP 2: Load IDs of Sales Orders modified based on the criteria
//Load Sales Orders updated in the last 7 days NewSalesList: LOAD
tSLSalesOrderID AS SLSalesOrderID
RESIDENT tSalesList WHERE DATE(tSLSalesOrderLastUpdated,'YYYY-MM-DD') >= DATE(TODAY(),'YYYY-MM-DD')-7;
Loop Through the list of Sales Orders and load the Sales Order Lines - This is where the failure occurs
//Load updated Sales Order Lines LET vLoop= 0; LET vRecord = 0; LET vSOID= 0; LET vTotalRows = NOOFROWS('NewSalesList'); Result is 9 IDs which is correct
FOR vLoop= 0 TO vTotalRows-1;
LET vSOID = PEEK('SLSalesOrderID',vLoop,'NewSalesList');
RestConnectorMasterTable: SQL SELECT "SaleID", "SaleOrderNumber", "Memo", "Status", "TotalBeforeTax", "Tax" AS "Tax_u0", "Total" AS "Total_u0", "__KEY_root", (SELECT "ProductID", "SKU", "Name", "Quantity", "Price", "Discount", "Tax", "AverageCost", "TaxRule", "Comment", "DropShip", "BackorderQuantity", "Total", "Sku", "__FK_Lines" FROM "Lines" FK "__FK_Lines") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION(Url "$(vURL)");
LET vLoop = vLoop + 1;
SLEEP(1000); Calls Limited in API service to 60 callsper minute which in itself is a nuisance 😞
NewSalesOrderLines: LOAD [ProductID], [SKU], [Name], [Quantity], [Price], [Discount], [Tax], [AverageCost], [TaxRule], [Comment], [DropShip], [BackorderQuantity], [Total], // [Sku], [__FK_Lines] AS [__KEY_root] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_Lines]);
LEFT JOIN (NewSalesOrderLines) LOAD [SaleID], [SaleOrderNumber], [SaleOrderNumber] AS SalesOrderNo, [Memo], [Status], [TotalBeforeTax], [Tax_u0], [Total_u0], [__KEY_root]
RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__KEY_root]);
DROP TABLE RestConnectorMasterTable;
//DROP TABLE SalesOrderLines;
I have verified, using the debugger that it loops through and call the service 9 times but it seems like the Sales Order Id does not load. I obtain the Sales Order ID using PEEK() with record number = vLoop.
yet if I call the API direct the missing sales orders do exist
Can anyone assist in helping me trouble shoot this issue please.