Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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;
// ==========================================================================================//
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).
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.
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
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!