How to write nested for loop

HI All,

I have below table , and I looking for student1 from blue team played football
student1 from green team played football etc

Student name game Team
student1 football Blue
student2 cricket green
student3 hokey red
student4 tennis Yellow
student5 football Blue
student6 cricket green
student7 hokey red
student8 tennis Yellow

Basically for every student need to count team and games. I am unable explain clearly, please help me

Comments

  • 1 Begin Error Handling; Action: Continue; Options: Task Status: Fail
    2 Connect to "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DefaultDir=C:\Users\Administrator\Documents\Automation Anywhere Files\Automation Anywhere\My Docs\PoC;Driver={Microsoft Text Driver (.txt; *.csv)};DriverId=27;FIL=text;FILEDSN=C:\Users\Administrator\Documents\Automation Anywhere Files\Automation Anywhere\My Docs\PoC\teams.csv.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"" Session:'Default'
    3 Connect to "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DefaultDir=C:\Users\Administrator\Documents\Automation Anywhere Files\Automation Anywhere\My Docs\PoC;Driver={Microsoft Text Driver (
    .txt; .csv)};DriverId=27;FIL=text;FILEDSN=C:\Users\Administrator\Documents\Automation Anywhere Files\Automation Anywhere\My Docs\PoC\teams.csv.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"" Session:'Default2'
    4 Connect to "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DefaultDir=C:\Users\Administrator\Documents\Automation Anywhere Files\Automation Anywhere\My Docs\PoC;Driver={Microsoft Text Driver (
    .txt; *.csv)};DriverId=27;FIL=text;FILEDSN=C:\Users\Administrator\Documents\Automation Anywhere Files\Automation Anywhere\My Docs\PoC\teams.csv.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"" Session:'Default3'
    5 Execute SQL Statement: 'select distinct Student from teams.csv;' Session:'Default'
    6 Start Loop "Each row in SQL Data Set Session: Default"
    7 Variable Operation: $Dataset Column(1)$ To $vName$
    8 Execute SQL Statement: 'select distinct Sport from teams.csv where Student = '$vName$';' Session:'Default2'
    9 Start Loop "Each row in SQL Data Set Session: Default2"
    10 Variable Operation: $Counter$ To $vSportCount$
    11 End Loop
    12 Execute SQL Statement: 'select distinct Team from teams.csv where Student = '$vName$';' Session:'Default3'
    13 Start Loop "Each row in SQL Data Set Session: Default3"
    14 Variable Operation: $Counter$ To $vTeamCount$
    15 End Loop
    16 Message Box: "$vName$$vSportCount$$vTeamCount$"
    17 End Loop
    18 Disconnect from database Session:'Default'
    19 Disconnect from database Session:'Default2'
    20 Disconnect from database Session:'Default3'
    21 End Error Handling
    22 If $Error Line Number$ Not Equal To (<>) "0" Then
    23 Message Box: "$Error Description$ on $Error Line Number$"
    24 End If

    Ordinarily you would be able to do select count(distinct team)... - but AA's SQL seems limiting here.

    Student,Sport,Team
    student1,Football,Blue
    student1,Football,Green
    student2,Cricket,Green
    student3,Hockey,Red
    student4,Tennis,Yellow
    student5,Football,Blue
    student6,Cricket,Green
    student7,Hockey,Red
    student8,Tennis,Yellow

Sign In or Register to comment.