FOR..NEXT LOOP is skipping record on incremental load

[zanderxhype]

Hello Everyone,

I have run into a bizarre issue during an incremental load scenario.

1. I have a Sales Order List table and a Sales Order Lines table which is loaded and stored into a QVD daily.
2. 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.
3. 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

1. Total Sales Orders in list: 593
2. TotalSales Orders modified since last reload: 9 (currently using today()-7 just so I get sufficient test data loaded.)
3. 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.

Steps:

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;

Step 3:

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');

LET vURL='https://inventory.dearsystems.com/ExternalApi/v2/sale/order?SaleID='&'$(vSOID)'&'&CombineAdditionalC...';

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 😞

NEXT vLoop;

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.

Zander

[Adorable09]

I have also tried to implement incremental load with for loop but not getting the correct data. It is skipping some records. 

Can anybody help on this? 

[stevejoyce]

You don't have to increment vLoop in your script (LET vLoop = vLoop + 1;).  "Next vLoop" increments it.  Remove the line "LET vLoop = vLoop + 1;".  Yes it's running right number of times, but I bet if you run a trace you'd see .

vSOID & vURL run 2x for the same SLSalesOrderID and missing the other half