Keep row number

Hi all,

Im having trouble making QTP do a For loop with stuff for 100 rows in a DataTable, then do different stuff 1 time and do the first stuff again for the next 100 rows (ie row 101-200), rinse and repeat with 100 rows each time. My problem is that it starts at row 2 after doing the code first time and I need it to continue from row 101 instead.

For clarification I added a msg box in the code and what im trying to accomplish is for that to add 1 each time, also between running full circle in the code (ie for each 100 rows). So lets say after doing 400 rows the msgbox should say 400 and the "do different stuff" code would then have been done 4 times.

Dim x

For x = 1 To 100

'Do stuff
msgbox DataTable.GlobalSheet.GetCurrentRow

Next

'Do different stuff 1 time and then start the For loop again from row 101

 

Is it in any way possible to "save" the row number and continue from that after doing the full code?

I would really appreciate any effort spend helping :-)

 

Using QTP 11 and in settings its set to run on all rows

 

Parents
  • I'm puzzled, why not save the last used rownumber in a variable? And the run from saverownumber 1?

    If a variable can't be used, you could write the value of course to a datatable, and pick it up again...

  • Thanks for the response :)

     

    I did try to save rownumber in a variable, but variables seem to get "reset" as well after each run.

     

    Im not familiar with saving to databases in QTP, but I will try to look into that and see if that can do the trick.

  • Here is a quick way to do it.  It's probably not the best way, but it may work in your situation.  In your data spreadsheet, on every hundreth row set a flag that let's QTP know to "do different stuff 1 time" as you mentioned.  In my example below, I created a new column in the spreadsheet called "MyColumnFlag" on each hundreth row I put the text "END" in the Excel cell.  If you have lots of rows (like in the 1,000s) then this might not be a good approach, but if you're talking a few hundred or less then it may work for you.  Be sure to set the run time setting to "Run one iteration only".  Hope this helps.

     

    Dim x, rowcount
    DataTable.ImportSheet "C:\Temp\MySpreadsheetExample.xls""Global""Global"
    rowcount = DataTable.GlobalSheet.GetRowCount
    For x = 1 To rowcount
        DataTable.GetSheet("Global").SetCurrentRow(x)
        'Do stuff
        If DataTable.Value("MyColumnFlag""Global") = "END" Then
            msgbox "This was the hundredth row."
            'Do different stuff 1 time and then start the For loop again from row 101
        End If
        DataTable.GetSheet("Global").SetNextRow
    Next

  • The 100th cell value might work, but I do have a lot of rows, like 10.000, but of cause its just 1 time i have to set it up.

     

    If the other suggestions i havent tried yet fails me, i might go for that one.

     

    Still waiting for the installation of UTF.

     

    Thanks :)

  • The 100th cell value might work, but I do have a lot of rows, like 10.000, but of cause its just 1 time i have to set it up.

     

    If the other suggestions i havent tried yet fails me, i might go for that one.

     

    Still waiting for the installation of UTF.

     

    Thanks :)

  • The 100th cell value might work, but I do have a lot of rows, like 10.000, but of cause its just 1 time i have to set it up.

     

    If the other suggestions i havent tried yet fails me, i might go for that one.

     

    Still waiting for the installation of UTF.

     

    Thanks :)

  • I have a suggestion, but need a clarification first.

    Is the thing you do every 100th row the same thing or different?

    I think you could maybe do this by using a Global datasheet and an Action datasheet.

     

    The Action datasheet would include the 10,000 rows for which you want to perform some activity for every block of 100 rows. That activity would be defined in a separate Action to which that datasheet would be associated.

     

    The Global datasheet would have one row for every 100 rows in your Action sheet. So, if your Action sheet has 10,000 rows, then your Global sheet would have 100.

     

    The Global sheet drives the overall iteration of your main test script. Within the main test script, in each of its iterations you execute the activity that you want to execute for every 100th row. Then you insert a call to your Action, telling it to iterate through rows n through n 99 of your Action datasheet.

     

    Your main test would iterate 100 times (the number of rows in the Global datasheet). Within each of those iterations, you would iterate 100 times through the  secondary Action. You could even make the starting and ending rows for the secondary action part of the row data in your Global sheet.

     

    I do something similar in my testing, but I use an External Action and have to use commands to load the DataTable that goes with the External Action. I choose to store my datatables separate from my test scripts. You might be able to do this with datatables stored as part of the test script, though.

  • I have a suggestion, but need a clarification first.

    Is the thing you do every 100th row the same thing or different?

    I think you could maybe do this by using a Global datasheet and an Action datasheet.

     

    The Action datasheet would include the 10,000 rows for which you want to perform some activity for every block of 100 rows. That activity would be defined in a separate Action to which that datasheet would be associated.

     

    The Global datasheet would have one row for every 100 rows in your Action sheet. So, if your Action sheet has 10,000 rows, then your Global sheet would have 100.

     

    The Global sheet drives the overall iteration of your main test script. Within the main test script, in each of its iterations you execute the activity that you want to execute for every 100th row. Then you insert a call to your Action, telling it to iterate through rows n through n 99 of your Action datasheet.

     

    Your main test would iterate 100 times (the number of rows in the Global datasheet). Within each of those iterations, you would iterate 100 times through the  secondary Action. You could even make the starting and ending rows for the secondary action part of the row data in your Global sheet.

     

    I do something similar in my testing, but I use an External Action and have to use commands to load the DataTable that goes with the External Action. I choose to store my datatables separate from my test scripts. You might be able to do this with datatables stored as part of the test script, though.

  • I have a suggestion, but need a clarification first.

    Is the thing you do every 100th row the same thing or different?

    I think you could maybe do this by using a Global datasheet and an Action datasheet.

     

    The Action datasheet would include the 10,000 rows for which you want to perform some activity for every block of 100 rows. That activity would be defined in a separate Action to which that datasheet would be associated.

     

    The Global datasheet would have one row for every 100 rows in your Action sheet. So, if your Action sheet has 10,000 rows, then your Global sheet would have 100.

     

    The Global sheet drives the overall iteration of your main test script. Within the main test script, in each of its iterations you execute the activity that you want to execute for every 100th row. Then you insert a call to your Action, telling it to iterate through rows n through n 99 of your Action datasheet.

     

    Your main test would iterate 100 times (the number of rows in the Global datasheet). Within each of those iterations, you would iterate 100 times through the  secondary Action. You could even make the starting and ending rows for the secondary action part of the row data in your Global sheet.

     

    I do something similar in my testing, but I use an External Action and have to use commands to load the DataTable that goes with the External Action. I choose to store my datatables separate from my test scripts. You might be able to do this with datatables stored as part of the test script, though.

  • I will try to clarify the 100 /1 row "stuff" and why i need to do it this way.

     

    The automation im doing is creating user data in SAP and each row is setting data in SAP for 1 user.

     

    Problem is that there is some kind of memory leak in this process in SAP, so for each user the step takes more time and after 100 users or so, SAP simply breaks down due to lack of memory. So the "do different stuff" I have to do for each 100th row is actually shutting down SAP and reopening it, so it can start with fresh memory with user number 101. This means there isnt actually a row for the "do different stuff".

     

    So I need to set data in SAP for 100 users (rows) then shut down SAP and reopen it, then continue from last row -  rinse and repeat.

  • I will try to clarify the 100 /1 row "stuff" and why i need to do it this way.

     

    The automation im doing is creating user data in SAP and each row is setting data in SAP for 1 user.

     

    Problem is that there is some kind of memory leak in this process in SAP, so for each user the step takes more time and after 100 users or so, SAP simply breaks down due to lack of memory. So the "do different stuff" I have to do for each 100th row is actually shutting down SAP and reopening it, so it can start with fresh memory with user number 101. This means there isnt actually a row for the "do different stuff".

     

    So I need to set data in SAP for 100 users (rows) then shut down SAP and reopen it, then continue from last row -  rinse and repeat.

  • I will try to clarify the 100 /1 row "stuff" and why i need to do it this way.

     

    The automation im doing is creating user data in SAP and each row is setting data in SAP for 1 user.

     

    Problem is that there is some kind of memory leak in this process in SAP, so for each user the step takes more time and after 100 users or so, SAP simply breaks down due to lack of memory. So the "do different stuff" I have to do for each 100th row is actually shutting down SAP and reopening it, so it can start with fresh memory with user number 101. This means there isnt actually a row for the "do different stuff".

     

    So I need to set data in SAP for 100 users (rows) then shut down SAP and reopen it, then continue from last row -  rinse and repeat.

Reply
  • I will try to clarify the 100 /1 row "stuff" and why i need to do it this way.

     

    The automation im doing is creating user data in SAP and each row is setting data in SAP for 1 user.

     

    Problem is that there is some kind of memory leak in this process in SAP, so for each user the step takes more time and after 100 users or so, SAP simply breaks down due to lack of memory. So the "do different stuff" I have to do for each 100th row is actually shutting down SAP and reopening it, so it can start with fresh memory with user number 101. This means there isnt actually a row for the "do different stuff".

     

    So I need to set data in SAP for 100 users (rows) then shut down SAP and reopen it, then continue from last row -  rinse and repeat.

Children
  • I believe that you could use my suggestion, then.

  • I believe that you could use my suggestion, then.

  • I believe that you could use my suggestion, then.

  • I finally got UFT installed so have been messing a bit with this again. Its quite different looking than the QTP version i had, so trying to get familiar with it.

     

    I did try to set it up as  you suggested Trudy Claspill,  but cant get it to work as intended.

     

    Could you please clarify these 2 sentenses:

     
    --------------------------------------------------------------------------------
    Trudy Claspill wrote:

    Within the main test script, in each of its iterations you execute the activity that you want to execute for every 100th row.
    --------------------------------------------------------------------------------

     

    As I can see it I cant set up code in the actual main script as it only consists of Start - Actions - End. i Can enter code in the actions, but not anywhere else, but maybe im missing something, as I am very new to QTP/UFT. Where do you mean I should write the "do different stuff" code?


    --------------------------------------------------------------------------------
    Trudy Claspill wrote:
    Then you insert a call to your Action, telling it to iterate through rows n through n 99 of your Action datasheet.
    --------------------------------------------------------------------------------

     

    Do you mean an actual loop in the Action1 og do you just mean that this happens by itself due to the setup with 1 row in global for each 100 in the action1?

     

    You also mention a secondary action, which confuse me a bit. Do you have more than one action in the setup?

     

    Thanks very much for the suggestions and the effort helping :)

  • I finally got UFT installed so have been messing a bit with this again. Its quite different looking than the QTP version i had, so trying to get familiar with it.

     

    I did try to set it up as  you suggested Trudy Claspill,  but cant get it to work as intended.

     

    Could you please clarify these 2 sentenses:

     
    --------------------------------------------------------------------------------
    Trudy Claspill wrote:

    Within the main test script, in each of its iterations you execute the activity that you want to execute for every 100th row.
    --------------------------------------------------------------------------------

     

    As I can see it I cant set up code in the actual main script as it only consists of Start - Actions - End. i Can enter code in the actions, but not anywhere else, but maybe im missing something, as I am very new to QTP/UFT. Where do you mean I should write the "do different stuff" code?


    --------------------------------------------------------------------------------
    Trudy Claspill wrote:
    Then you insert a call to your Action, telling it to iterate through rows n through n 99 of your Action datasheet.
    --------------------------------------------------------------------------------

     

    Do you mean an actual loop in the Action1 og do you just mean that this happens by itself due to the setup with 1 row in global for each 100 in the action1?

     

    You also mention a secondary action, which confuse me a bit. Do you have more than one action in the setup?

     

    Thanks very much for the suggestions and the effort helping :)

  • I finally got UFT installed so have been messing a bit with this again. Its quite different looking than the QTP version i had, so trying to get familiar with it.

     

    I did try to set it up as  you suggested Trudy Claspill,  but cant get it to work as intended.

     

    Could you please clarify these 2 sentenses:

     
    --------------------------------------------------------------------------------
    Trudy Claspill wrote:

    Within the main test script, in each of its iterations you execute the activity that you want to execute for every 100th row.
    --------------------------------------------------------------------------------

     

    As I can see it I cant set up code in the actual main script as it only consists of Start - Actions - End. i Can enter code in the actions, but not anywhere else, but maybe im missing something, as I am very new to QTP/UFT. Where do you mean I should write the "do different stuff" code?


    --------------------------------------------------------------------------------
    Trudy Claspill wrote:
    Then you insert a call to your Action, telling it to iterate through rows n through n 99 of your Action datasheet.
    --------------------------------------------------------------------------------

     

    Do you mean an actual loop in the Action1 og do you just mean that this happens by itself due to the setup with 1 row in global for each 100 in the action1?

     

    You also mention a secondary action, which confuse me a bit. Do you have more than one action in the setup?

     

    Thanks very much for the suggestions and the effort helping :)

  • In UFT, the steps you want to execute once for every group of 100 records should go into the Main subroutine of Action1 of your test.

     

    The steps you want to execute for each of the rows in the group of 100 would be coded into a new Action - Action2. Set up a new action by selecting Design > Call to New Action. Put the code that will be repeated for ever user record into Action2. That was the item I was referring to as the "secondary action".

     

    After creating Action2, go back to Action1. Select Design > Call to Existing Action.  For "From test" select <Current Test>. For "Action" select Action2. This will insert a line in Action1 that looks something like

     

    RunAction "Action2", oneIteration.

     

    You'll want to look up the RunAction statement in online Help to get more information on it.

     

    You'll see a new datatable named Action2 added to your test. Enter your 10000 user rows into this table.

     

    Now, if you have 100 rows in your Global datasheet, then that means Action1 will get called 100 times. Each of those times that Action1 gets executed, it will also execute Action2. You can modify the RunAction call to tell it to run against a different set of rows. I would suggest that as part of your Global datasheet you make one column the row number for the first row in the group of 100, and another column the last row number in the group of 100. So, Global would look something like

     

    GlobalIteration,Action2FirstRow,Action2LastRow

    1,1,100

    2,101,201

    3,202,302

    ...

     

    Use the Action2FirstRow and Action2LastRow values as part of the RunAction statement to indicate which rows in Action2 datatable should be processed during each iteration.

     

    There shouldn't need to be any loop in Action1. The Global datatable rows should drive the number of iterations (1 for every 100 user records).

     

    I haven't explicitly coded this out to prove it works. It should work, but you may need to do some tweaks and experimentation to get it just right.

     

     

  • In UFT, the steps you want to execute once for every group of 100 records should go into the Main subroutine of Action1 of your test.

     

    The steps you want to execute for each of the rows in the group of 100 would be coded into a new Action - Action2. Set up a new action by selecting Design > Call to New Action. Put the code that will be repeated for ever user record into Action2. That was the item I was referring to as the "secondary action".

     

    After creating Action2, go back to Action1. Select Design > Call to Existing Action.  For "From test" select <Current Test>. For "Action" select Action2. This will insert a line in Action1 that looks something like

     

    RunAction "Action2", oneIteration.

     

    You'll want to look up the RunAction statement in online Help to get more information on it.

     

    You'll see a new datatable named Action2 added to your test. Enter your 10000 user rows into this table.

     

    Now, if you have 100 rows in your Global datasheet, then that means Action1 will get called 100 times. Each of those times that Action1 gets executed, it will also execute Action2. You can modify the RunAction call to tell it to run against a different set of rows. I would suggest that as part of your Global datasheet you make one column the row number for the first row in the group of 100, and another column the last row number in the group of 100. So, Global would look something like

     

    GlobalIteration,Action2FirstRow,Action2LastRow

    1,1,100

    2,101,201

    3,202,302

    ...

     

    Use the Action2FirstRow and Action2LastRow values as part of the RunAction statement to indicate which rows in Action2 datatable should be processed during each iteration.

     

    There shouldn't need to be any loop in Action1. The Global datatable rows should drive the number of iterations (1 for every 100 user records).

     

    I haven't explicitly coded this out to prove it works. It should work, but you may need to do some tweaks and experimentation to get it just right.

     

     

  • In UFT, the steps you want to execute once for every group of 100 records should go into the Main subroutine of Action1 of your test.

     

    The steps you want to execute for each of the rows in the group of 100 would be coded into a new Action - Action2. Set up a new action by selecting Design > Call to New Action. Put the code that will be repeated for ever user record into Action2. That was the item I was referring to as the "secondary action".

     

    After creating Action2, go back to Action1. Select Design > Call to Existing Action.  For "From test" select <Current Test>. For "Action" select Action2. This will insert a line in Action1 that looks something like

     

    RunAction "Action2", oneIteration.

     

    You'll want to look up the RunAction statement in online Help to get more information on it.

     

    You'll see a new datatable named Action2 added to your test. Enter your 10000 user rows into this table.

     

    Now, if you have 100 rows in your Global datasheet, then that means Action1 will get called 100 times. Each of those times that Action1 gets executed, it will also execute Action2. You can modify the RunAction call to tell it to run against a different set of rows. I would suggest that as part of your Global datasheet you make one column the row number for the first row in the group of 100, and another column the last row number in the group of 100. So, Global would look something like

     

    GlobalIteration,Action2FirstRow,Action2LastRow

    1,1,100

    2,101,201

    3,202,302

    ...

     

    Use the Action2FirstRow and Action2LastRow values as part of the RunAction statement to indicate which rows in Action2 datatable should be processed during each iteration.

     

    There shouldn't need to be any loop in Action1. The Global datatable rows should drive the number of iterations (1 for every 100 user records).

     

    I haven't explicitly coded this out to prove it works. It should work, but you may need to do some tweaks and experimentation to get it just right.

     

     

  • Thank you again Trudy

     

    I will try to follow your very elaborate guide once i get time to mess with this again, but that might take a while.

     

    I will post my result here afterwards and, hopefully, mark this tread as solved :)

     

    Kudos given to all with suggestions