Proper syntax for FOR..NEXT loop and LOAD statement

[#1]

We break our sales down into five stages, 00, 20, 40, 60, 80. I have to find out how long it takes between each stage, and set the background colour in red for that one. I have a table created in my script, OM, that includes

OM:

Select

Company,

OPID, <- Opp ID, a unique 16-char field that identifies each opp'y

Stage,

OnDate,

(other fields)

from database

ORDER BY

Company, OPID, OnDate

Now, each company can have many opportunities, and most opportunities do NOT include all the five stages, so I wanted to do the calculation in the script by first ensuring that two adjacent records are for the same company and the same OPID. If so, I subtract one from the other. I have immediately under my ORDER BY statement above

UNQUALIFY OPID;

For i = 0 to NoOfRows('OM')-1

     IF peek('COMPANY',i,'OM') = peek('COMPANY',i+1,'OM') THEN

           IF peek('OPID',i,'OM') = peek('OPID',i+1,'OM') THEN

          StDiff: //Note: I tried with and without this table label. Same error each time

          LOAD

          OPID, // loaded to link files

          peek('OnDate',i+1,'OM')-peek('OnDate',i,'OM') as StageDiff,

          *;

      ENDIF

ENDIF

Next i

This completes without error, but the field "StageDiff" is nowhere to be found.

Can I use an IF THEN ELSE within a FOR NEXT loop?

Can I have a LOAD statement inside an IF THEN ELSE?

Any other ideas on how to do this correctly?

[sunny_talwar]

I don't see why if then won't work within FOR loop. but to get the syntax right, I would prefer having the script so that I can test run it. I know some other experts may be able to help you just by looking at the code, but if possible would you be able to share a app with some data and the above mentioned code where it doesn't seem to be working?

Best,

Sunny

[Anonymous]

Look for below example;

Sub LoadTableNames
SQLTableList:
LOAD
Name AS TableNames;
SELECT Name FROM ContosoRetailDW.sys.tables;

End Sub;

// ======================================================//

Call LoadTableNames;

// ======================================================//

Let vTableCount = NoOfRows(‘SQLTableList’);

// ======================================================//

Sub LoadTableData

For i = 0 To $(vTableCount) – 1
LET vMyTableName = Peek(‘TableNames’, $(i), ‘SQLTableList’);
$(vMyTableName):
SQL SELECT * FROM ContosoRetailDW.dbo.$(vMyTableName);
STORE $(vMyTableName) INTO $(Directory)$(vMyTableName).QVD;
DROP Table $(vMyTableName);
Next i

End Sub;

// ==========================================================================================//

Call LoadTableData;

// ==========================================================================================//

[#4]

thanks, Imran, but I think my issue has to do with the fact that my LOAD statement is nested in two IF statements,

which I don't see in your example above.

As I said, my script completes without error, but "StageDiff" does not get added to either the existing OM table, nor does it get added to the StDiff: table I tried to create (and as I noted, regardless of whether that table label is present or not, the script completes but the data disappears).

[#5]

I made some changes in my code, and found the following.

First, the new code (contains some lines so I could watch the debugger)

In a limited load using the debugger, I noted that the first two lines of the OM file had the same company and the same OPID, so I would have expected that after the statement

     if peek('OPID',i,'OM')=peek('OPID",i+1,'OM)

it would try to execute the LOAD statement.However, the debugger just showed it skipping directly to the ENDIF's. Here's a screen grab from the debugger, showing COMP1=COMP2 AND OP1=OP2.

But the "THEN" statement (i.e. the LOAD block) is not executing. I can't figure out why, unless this is not supported, and the non-support is not documented.

[cwolf · Accepted solution]

Try:

OM:

Load

*,

if(Company=Previous(Company) and OPID=Previous(OPID),OnDate-Previous(OnDate)) as StageDiff

;

Select

Company,

OPID,

Stage,

OnDate,

(other fields)

from database

ORDER BY

Company, OPID, OnDate

[#7]

Thanks, Christian. I wasn't aware of the "Previous" function, and wouldn't have thought of it in any case, as the table is not sorted by company coming in. However, after looking at what you suggested, it dawned on me that the LOAD statement gets executed AFTER the OM table is built, and hence it would already be ordered by Company, OPID, OnDate.

thanks a bunch to everyone!