Compare two columns from two files and append the output in any of the two files as a new column

Question

csv file 1:

Scorecard_1,ZDTJ_PREV.EXT,12
Scorecard_2,ZACN_PREV.EXT,6
Scorecard_3,ABC.txt,8

Text File 2:

Acct_Bal_Non_Zero_Tgt.txt,7243
IDQ_HB1.txt,5380
IDQ_HB_LOGC.txt,5380
ZACN_PREV.EXT,4
ZDTJ_PREV.EXT,3
ABC.txt,10

Output:

Acct_Bal_Non_Zero_Tgt.txt,No_Match
    IDQ_HB1.txt,No_Match
    IDQ_HB_LOGC.txt,No_Match
    ZACN_PREV.EXT,New_File
    ZDTJ_PREV.EXT,New_File
    ABC.txt,Old_File

Logic: If there is a matching file present in the second file the compare the last column of both the file. If last column in file one is greater than the last column of second file then the file is new else the file is old.

My task is to identify if the file is of current day or previous day based on the Interval ( which is last column of the first file. My approach is very trivial and I know there is a easier way through awk.

So far I have tried below:

File_in_CSV=$(cat file_1.csv | awk -F "," '{ print $3 }' | tail --lines=+1 | sort -u | uniq )
File_in_age_file=$(cat File_2.txt | awk -F "," '{ print $1 }' | tail --lines=+1 | sort -u | uniq )
Age=$(cat All_Files_Age.txt | awk -F "," '{ print $2 }' | tail --lines=+1 | sort -u | uniq )
Interval=$(cat scorecard_file_details.csv | awk -F "," '{ print $4 }' | tail --lines=+1 | sort -u | uniq )
for file in $File_in_CSV; do
if [[ "$file" = $File_in_age_file && $Age>$Interval ]]; then
printf "%s\n" "File is of Previous Day!"
else
printf "%s\n" "File is of Current Day!"
fi
done

I also want this flag of Previous day or current day to be appended as one column in any of the files. Thanks for your help on this!!

Example of expected Output is :

FileName, Flag
ABC.txt, New_File/Old_File

Thanks for sharing your efforts in your question, keep it up. Could you please do mention expected sample output in your question along with logic of getting it(in your question please not in comments) for better understanding of question. — RavinderSingh13
@RavinderSingh13-- Thanks! Mentioned the expected output and the logic is mentioned in my code. I just want to match those two columns IF ( Filename== Filename && Col4 >Col 2) Then New_File Else Old_File — Amit
sure but It will be more clear if you mention it in writing in your question(along with sample expected output) with details for better understanding purposes, cheers. — RavinderSingh13

RavinderSingh13 RavinderSingh13 · Accepted Answer · 2020-11-20T09:02:34

Could you please try following, written and tested with shown samples in GNU awk.

awk '
BEGIN{
  FS=OFS=","
}
FNR==NR{
  arr[$2]=$NF
  next
}
{
  print $1,(($1 in arr)?($NF>arr[$1]?"Old_file":"New_File"):"No_Match")
}
'  Input_file1  Input_file2

Explanation: Adding detailed explanation for above.

awk '                           ##Starting awk program from here.
BEGIN{                          ##Starting BEGIN section of this program from here.
  FS=OFS=","                    ##Setting field separator and outpout field separator as comma.
}
FNR==NR{                        ##Checking condition if FNR==NR when Input_file1 is being read.
  arr[$2]=$NF                   ##Creating arr with index of 2nd field and having value as last field.
  next                          ##next will skip further statements from here.
}
{
  print $1,($1 in arr)?($NF>arr[$1]?"Old_file":"New_File"):"No_Match"
                                ##printing 1st field and checking condition if 1st field is present in arr then check
                                ##if last field is greater than arr value then print Old_file else print
                                ##New_file OR if 1st field is NOT in arr then print No_Match.
}
'  Input_file1  Input_file2     ##Mentioning Input_file names here.

Compare two columns from two files and append the output in any of the two files as a new column

1 Answers