Combination of two columns from text file and finding count with reference to third column in Perl

I am trying to read the in.txt file and generate an output file out.txt Using Perl. I tried with Hashes but not getting exact output.

Is there a way to do this in Perl.

Combination of two columns and providing comments on the basis of third column.

in.txt

Template,Account,Active
123456,123,N
123456,456,Y
321478,456,Y
123456,123,N
321478,456,Y

out.txt

Account,Template,Active,NotActive
123,123456,0,2
456,321478,2,0
456,123456,1,0

my $filename = 'input.txt';
my %yhash;
my %nhash;
if (open(my $ifh, '<:encoding(UTF-8)', $filename)) {
    while (my $row = <$ifh>) {
    next if ($row =~ /^#/m);
    chomp $row;
    my @values = split(',',$row);
    my $value = join '',@values ;
    my $lastchar = substr $value , -1;
    my $firstval = substr $value ,0,9;
    if ($lastchar eq "N"){
              if (exists($nhash{firstval})){ $nhash{firstval}++; }
              $nhash{$firstval}++;
    }elsif($lastchar eq "Y"){
              if (exists($yhash{firstval})){ $yhash{firstval}++; }
              $yhash{$firstval}++;
    }else{
             print "nothin\n";

    }
    }
    close $ifh;
    } else {
    warn "Could not open file '$filename' $!";
    }


   open(FH, '>', 'out.txt') or die $!;
   print FH "Account,Template,Active,NotActive\n";
   while (my ($key, $value) = each(%nhash)) {
    my $account = substr $key ,6,3;
    my $template = substr $key ,0,6;
    my $active = "0";
    my $notactive = "$value";
    print FH "$account,$template,$active,$notactive \n";
   }
  while (my ($key, $value) = each(%yhash)) {
    my $account = substr $key ,6,3;
    my $template = substr $key ,0,6;
    my $active = "$value";
    my $notactive = "0";
    print FH "$account,$template,$active,$notactive \n";
  }
  close (FH);