Look, before you start to answer the question how to do a thing, you need to answer the
1) Rename spreadsheets, don't call it local and global, give them some meaningful name, find the name based on it business purpose, for example AccountDetails MonthlyPaymentDetails 2) You will need a function which search in MonthlyPaymentDetails and return set of results (you will need to choose format of return results array, or dictionary, or parameters ByRef) Ex. Function GetMonthlyPaymentDetails(SpreadsheetsName, ProposedMonthlyPayment) ==> ResultArray Once you do it, the question how to write working code will very simple. I think the script ill run very slow, if you want to speed it up you can load MonthlyPaymentDetails table in a Dictionary (where the key will be ProposedMonthlyPayment) and read the data from memory, in this case you may need function Function TableToDictionary(TableName, KeyColumnName) ==> Dictionary Roman On Mon, Dec 7, 2009 at 8:02 PM, <[email protected]> wrote: > Paven, Any suggestion will be appreciated. > > On Fri, Dec 4, 2009 at 8:18 PM, <[email protected]> wrote: > >> Pavan, >> Thank you for your response. I will check your solution tomorrow when I >> access to QTP. In the mean time here is my original problem: >> >> I have 42,000 rows of records in GlobalSheet with following columns: >> >> Account # Original Principal Amount Original Interest Rate >> Current Monthly Payment Proposed Monthly Payment >> >> 00001 200,000.00 >> 10.500% >> 1120.00 450.00 >> 00002 170,000.00 >> 9.750% >> 875.00 400.00 >> ...... >> ...... >> ....... >> 42000 285,000.00 >> 7.50% 1400.00 >> 700.00 >> >> >> ======================================================================================= >> >> And I have 510 rows of records in LocalSheet with following column: >> >> Modified Interest Rate Proposed Monthly Payment Term >> Months Forbearance Amount Modified Principal Amount >> 12.000% >> 1,400.00 340 >> 0.00 xxx,xxx.xx >> 11.875% 1,392.00 >> 340 0.00 >> xxx,xxx.xx >> ...... >> ....... >> 02.000% >> 741.00 340 >> 0.00 >> xxx,xxx.xx >> 02.000% >> 732.00 >> 341 0.00 >> xxx,xxx.xx >> 02.000% 726.00 >> 342 0.00 xxx,xxx.xx >> ....... >> ....... >> 02.000% 541.00 >> 480 0.00 >> xxx,xxx.xx >> 02.000% >> 537.00 >> 480 1000.00 xxx,xxx.xx >> 02.000% >> 526.00 480 >> 2000.00 xxx,xxx.xx >> ....... >> ....... >> ....... >> >> >> ============================================================================================= >> >> My script should read the 1st record from GlobalSheet and find the >> Modified Interest Rate, Modified Term Month, Forbearance Amount, Modified >> Principal Amount from LocalSheet based on the Proposed Monthly Payment of >> GlobalSheet by scanning from row 1. Once all the information are captured >> then the scrip should read the 2nd record of GlobalSheet and should do the >> same on LocalSheet to find the necessary info and so on ...... >> >> Hope this will give you little background about what I am trying to >> accomplish. >> Thanks you for your all cooperation. >> Razzaque >> >> >> >> On Fri, Dec 4, 2009 at 9:30 PM, Pavan Turlapati <[email protected] >> > wrote: >> >>> Hi Razza, >>> As far as per understanding, you want to get the local sheet value >>> and compare with some number(here 999) and you want to do this with >>> the GlobalSheet row count number of times. >>> If I am right, check this code it may help you. >>> >>> For k=1 to DataTable.GetSheet(dtGlobalSheet).GetRowCount >>> x=(DataTable.Value("Column1",dtLocalSheet)) >>> If x<999 Then >>> Reporter.ReportEvent micPass, "Run >>> Status", "Script is running >>> fine" 'For example row 7 has value < 999 in this iteration >>> Else >>> Reporter.ReportEvent micFail, "Run >>> Status", "Script is not running >>> fine" 'For example row 7 has value < 999 in this iteration >>> End If >>> DataTable.SetCurrentRow(k+1) >>> Next >>> >>> >>> If you are using globalsheet just for looping (ignore global sheet) >>> and you are interested in looping around local sheet; >>> >>> For k=1 to DataTable.GetSheet(dtLocalSheet).GetRowCount >>> x=(DataTable.Value("LocalA",dtLocalSheet)) >>> If x<999 Then >>> Reporter.ReportEvent micPass, "Run >>> Status", "Script is running >>> fine" 'For example row 7 has value < 999 in this iteration >>> Else >>> Reporter.ReportEvent micFail, "Run >>> Status", "Script is not running >>> fine" 'For example row 7 has value < 999 in this iteration >>> End If >>> DataTable.SetCurrentRow(k+1) >>> Next >>> >>> Revert back, if any thing is required. >>> >>> On Dec 5, 2:51 am, [email protected] wrote: >>> > Thank you Bhavin. I tried that way. Problem is I do always have +ve >>> data, >>> > i.e., always have data smaller than 999, which means else part never >>> > executes. Therefore j never resets to 1. >>> > >>> > On Fri, Dec 4, 2009 at 3:33 PM, bhavin v patel <[email protected]> >>> wrote: >>> > >>> > > You just need to reset the row number for the second iteration, I >>> just >>> > > put the j=1 in else condition so when next time it will run the value >>> > > will reset to first row. I also run the smoke test to your code so >>> > > its working fine. I hope it will work. Please let us know >>> > > Regards >>> > > Bhavin >>> > >>> > > i = 1 >>> > > DataTable.GlobalSheet.SetCurrentRow(i) >>> > > Do While i < 10 >>> > > j = 1 >>> > > DataTable.LocalSheet.SetCurrentRow(j) >>> > > Do While j < 20 >>> > > If DataTable("Column1", dtLocalSheet) < 999 Then >>> > > Reporter.ReportEvent micPass, "Run Status", "Script is running >>> > > fine" 'For >>> > > example row 7 has value < 999 in this iteration >>> > > Else >>> > > j=1 >>> > > Reporter.ReportEvent micPass, "DataTable Issue", "Hello QTP Guru, >>> > > can you >>> > > solve my issue?" >>> > > End If >>> > > Loop >>> > > j = j + 1 >>> > > Loop >>> > > i = i + 1 >>> > >>> > > On Dec 4, 9:11 am, [email protected] wrote: >>> > > > Any suggestion/help will be appreciated for the following issue. >>> Thank >>> > > you. >>> > >>> > > > On Thu, Dec 3, 2009 at 12:23 PM, <[email protected]> wrote: >>> > > > > Hi, >>> > > > > I am using both Global and Local sheet in my test. >>> > > > > I have a set of data in GlobalSheet and based on data on >>> GlobalSheet my >>> > > > > script uses LocalSheet to complete testing. >>> > > > > Here is the problem: >>> > >>> > > > > Script reads row 1 of GlobalSheet and executes from row 1 of >>> localSheet >>> > > (I >>> > > > > have 400 rows in LocalSheet) and complete executing at row 57 of >>> > > LocalSheet >>> > > > > (as expected) >>> > >>> > > > > In next iteration scripts reads row 2 of GlobalSheet and executes >>> from >>> > > row >>> > > > > 57 of LocalSheet. >>> > >>> > > > > How can I reset LocalSheet to row 1 at runtime so that at 2nd >>> iteration >>> > > > > scripts executes from row 1 of LocalSheet. >>> > >>> > > > > Here is an example: >>> > >>> > > > > i = 1 >>> > > > > DataTable.GlobalSheet.SetCurrentRow(i) >>> > > > > Do While i < 10 >>> > > > > j = 1 >>> > > > > DataTable.LocalSheet.SetCurrentRow(j) >>> > > > > Do While j < 20 >>> > > > > If DataTable("Column1", dtLocalSheet) < 999 Then >>> > > > > Reporter.ReportEvent micPass, "Run Status", "Script is running >>> fine" >>> > > > > 'For example row 7 has value < 999 in this iteration >>> > > > > Else >>> > > > > Reporter.ReportEvent micPass, "DataTable Issue", "Hello QTP >>> Guru, >>> > > can >>> > > > > you solve my issue?" >>> > > > > End If >>> > > > > Loop >>> > > > > j = j + 1 >>> > > > > Loop >>> > > > > i = i + 1 >>> > >>> > > > > At iteration 2 script executes from row 7 of Local Sheet. >>> > >>> > > > > Any help will be appreciated. Thanks. >>> > >>> > > > > MZR- Hide quoted text - >>> > >>> > > > - Show quoted text - >>> > >>> > > -- >>> > > You received this message because you are subscribed to the Google >>> > > "QTP - HP Quick Test Professional - Automated Software Testing" >>> > > group. >>> > > To post to this group, send email to [email protected] >>> > > To unsubscribe from this group, send email to >>> > > [email protected]<mercuryqtp%[email protected]> >>> <mercuryqtp%[email protected]<mercuryqtp%[email protected]> >>> > >>> > > For more options, visit this group at >>> > >http://groups.google.com/group/MercuryQTP?hl=en >>> >>> -- >>> You received this message because you are subscribed to the Google >>> "QTP - HP Quick Test Professional - Automated Software Testing" >>> group. >>> To post to this group, send email to [email protected] >>> To unsubscribe from this group, send email to >>> [email protected]<mercuryqtp%[email protected]> >>> For more options, visit this group at >>> http://groups.google.com/group/MercuryQTP?hl=en >>> >> >> > -- > You received this message because you are subscribed to the Google > "QTP - HP Quick Test Professional - Automated Software Testing" > group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected]<mercuryqtp%[email protected]> > For more options, visit this group at > http://groups.google.com/group/MercuryQTP?hl=en > -- You received this message because you are subscribed to the Google "QTP - HP Quick Test Professional - Automated Software Testing" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/MercuryQTP?hl=en
