The ultimate goal of this project is to initiate the loop function. However, the problem so far has been that it is not looping properly. Based off my qvd file, it is only looping once, and for this project to be successful it needs to loop three times. I am looping five rows at a time and if anyone can help it will be greatly appreciated. Here is the data and the script that I wrote.
BOX:
LOAD * INLINE [RowNo,BOXName, VOLCnt
1, "MRC", 49152
2, "TTN", 49152
3, "PLT", 12288
4, "GLD", 12288
5, "SVR", 12288
6, "IRN", 12288
7, "XTX", 62415
8, "ZYX", 62416
9, "ABC", 19835
10, "DEF", 54248
11, "GHI", 77578
12, "XYZ", 62415
];
STORE BOX INTO [..\..\Data Source\QVD\NextGen\Box.qvd];
DROP TABLE BOX;
FOR i = $(minTransactionID) to $(minTransactionID)+5;
Boxes3: LOAD * FROM [..\..\Data Source\QVD\NextGen\Box.qvd] (qvd);
TempMinID:LOAD Min(RowNo) as MinRowID FROM [..\..\Data Source\QVD\NextGen\Box.qvd] (qvd);
Let minTransactionID = FieldValue('MinRowID',1);
DROP TABLE TempMinID;
LET Varx=$(minTransactionID);
LET Vary = $(Varx)+5;
Boxes4:
NoConcatenate
LOAD * FROM [..\..\Data Source\QVD\NextGen\Box.qvd] (qvd) WHERE RowNo >= $(Varx) AND RowNo < $(Vary);
STORE Boxes4 INTO [..\..\Data Source\QVD\NextGen\BoxStorage.qvd];
Boxes2:
Noconcatenate
LOAD * FROM [..\..\Data Source\QVD\NextGen\Box.qvd] (qvd) WHERE RowNo >= $(Vary) ;
STORE Boxes2 INTO [..\..\Data Source\QVD\NextGen\Box.qvd];
You're still not really explaining what you're trying to achieve here. It seems to me that looping is not the optimal way to approach the issue you've presented.
As others have noted, the script you've shown above doesn't work. A few points:
The minTransactionID variable isn't initialised.
Do you really want to loop from minTransactionID to minTransactionID + 5? I think not. I think what you want to do is loop from 1 to div(maxTransactionID - minTransactionID, 5) + 1 (3)
I don't think it's a great idea to modify minTransactionID within your loop. Set these before executing the loop and set other variables (varX and varY) within your loop.
When creating a script such as this, it's a good idea to build up gradually rather than code it all at once. That way you can debug issues such as the one with dropping your Boxes2 and Boxes4 tables.
I've taken a stab at what I think you might want (attached).
it might be useful if you explained what you're actually trying to achieve here. We have your source table, but what do you want your resulting tables to look like?
Also, one obvious issue here is that you create Boxes4 and Boxes2 within your loop but drop them outside the loop. Given that you use NOCONCATENATE, you'll end up with tables named Boxes2, Boxes2-1, Boxes2-2, etc, and also for Boxes4.
I thought I did explain it in the earlier paragraph, but to elaborate further, the purpose of the data is to loop over a limited amount of rows or in other words, I do not want to load an entire table I just want to load a few rows in the table. In this scenario I want the box qvd file to have row No 12, which is the last row and it proves that I successfully completed the loop. For privacy reasons, I can not go into the full detail of what I am doing, but the ultimate goal is to successfully do multiple loops. Unfortunately, based off the box qvd, it just loads row number 1 through 5.
I dropped them outside the loop because I kept on obtaining error messages when I dropped them in the loop.
If you run the load script in debug mode for a limited load of 10 rows, you can step through the script and see what is happening with your variables during the load in the debug windows.
It may be worth adding some trace commands to allow you to see the values of the variables whilst loading when checking the document log.
e.g. TRACE minTransactionID = [$(minTransactionID)] ;
When you first run the script it fails for me as minTransactionID is not set.
Assuming it is supposed to be 1 you are iterating 6 times (I=1 to 6). With each iteration you lose the first 5 rows of your data as you are storing back into Box only RowNo>Vary.
But your sample data only has 12 rows, so you run out of data part way through. Possibly that is why dropping tables Boxes4/Boxes2 failed & not dropping them may be why you are getting the odd results you are seeing.
You're still not really explaining what you're trying to achieve here. It seems to me that looping is not the optimal way to approach the issue you've presented.
As others have noted, the script you've shown above doesn't work. A few points:
The minTransactionID variable isn't initialised.
Do you really want to loop from minTransactionID to minTransactionID + 5? I think not. I think what you want to do is loop from 1 to div(maxTransactionID - minTransactionID, 5) + 1 (3)
I don't think it's a great idea to modify minTransactionID within your loop. Set these before executing the loop and set other variables (varX and varY) within your loop.
When creating a script such as this, it's a good idea to build up gradually rather than code it all at once. That way you can debug issues such as the one with dropping your Boxes2 and Boxes4 tables.
I've taken a stab at what I think you might want (attached).
