Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

FOR..NEXT LOOP is skipping record on incremental load

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.

counts.png

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.

 

loops.png

yetdirectcall.png if I call the API direct the missing sales orders do exist

 

Can anyone assist in helping me trouble shoot this issue please.

Zander

 

Labels (3)
0 Replies