Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Divide the value of the column if not NULL.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Divide the value of the column if not NULL.
# 1  
Old 05-07-2018
Divide the value of the column if not NULL.
File 1
---------
Code:
N_ACCT,CARD_TYPE,CARD_BAL,CUST_CODE
---------------------------------------------------
0301,38,0.00,10
0319,38,54422.92,10
0392,38,0.00,10
0418,38,35254.87,10
0442,38,0.00,10
0491,38,0.00,10
0558,38,45988.76,10
0616,38,0.00,10
0665,38,0.00,10
0699,38,0.00,10

File 2
---------------
Code:
N_ACCT,CARD_TYPE,CARD_BAL,CUST_CODE
--------------------------------------------------
0301,38,0.00,10
0319,38,56422.92,10
0392,38,0.00,10
0418,38,38254.87,10
0442,38,0.00,10
0491,38,0.00,10
0558,38,49988.76,10
0616,38,0.00,10
0665,38,0.00,10
0699,38,0.00,10


I have to apply below logic in the script.

Code:
If IsNull(File1.CARD_BAL) Then File1.CARD_BAL/2 Else (File 1.CARD_BAL+ File2.CARD_BAL)/2.

Finally output should be in below format
----------------------------------------

Code:
N_ACCT,CARD_TYPE,CARD_BAL,CUST_CODE
0301,38,0.00,10
0319,38,54422.92,10

Since I am not familiar with UNIX, appreciate your help on this.




Moderator's Comments:
Mod Comment Please use CODE (not HTML) tags as required by forum rules! Don't tag the entire text, just the code and data parts.
Last edited by RudiC; 05-07-2018 at 05:57 AM.. Reason: Changed HTML to CODE tags, excluded normal text.
# 2  
Old 05-07-2018
Given your (hypothetical?) IsNull function returns TRUE for Zero values and not, as is done usually in e.g. DB functions, for missing, undefined, or empty variables, the File1.CARD_BAL/2 is pointless, as it will return 0.00 again.
As you seem to want the arithmetic average of file1 and file2 fields, why then is the desired output's second line value 54422.92, and not 55422.92? And, where is the rest of the resulting output lines?
And, how are the files' records related, by line No.? By key (e.g. field 1) value? By what?
# 3  
Old 05-07-2018
Code:
awk '
NR < 3
FNR > 2 {line[$1]=$0; sum[$1]+=$3}
END {
   for (i in sum) {
      $0=line[i];
      $3=sprintf("%0.2f", sum[i] / 2);
      print $0;
   }
}
' FS=, OFS=, file1 file2

# 4  
Old 05-08-2018
While rdrtx1's proposal is based on the assumption that records are related by $1 key value, this one goes for the line No.:
Code:
awk '
NR == 1         {for (i=1; i<=NF; i++) if ($i == COL) TGF = i
                }
                {getline TMP < DFN
                 split (TMP, T)
                 if ($TGF+0 > 0) $TGF = ($TGF + T[TGF])/2
                }
1
' FS="," OFS="," COL="CARD_BAL" DFN="file2" file1
N_ACCT,CARD_TYPE,CARD_BAL,CUST_CODE
---------------------------------------------------
0301,38,0.00,10
0319,38,55422.9,10
0392,38,0.00,10
0418,38,36754.9,10
0442,38,0.00,10
0491,38,0.00,10
0558,38,47988.8,10
0616,38,0.00,10
0665,38,0.00,10
0699,38,0.00,10

This User Gave Thanks to RudiC For This Post:
jiam912
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Getting the lines with nth column non-null

Hi, I have a huge list of archives (.gz). Each archive is about 40MB. A file is generated every minute so if I want to analyze the data for 1 hour I get already 60 files for example. These are text files, ';' separated, each line having about 300 fields (columns). What I need to do is to... (11 Replies)
Discussion started by: Nenad
11 Replies

2. Shell Programming and Scripting

How to find null column?

hi Gurus, I need find the null column in a file. my file like below abc, ,cde,def abc,ded,cdd,def abc, ,ddd,ccd aaa,bbb,ccc,ddd basic, I need to find the lines which second column is null Thanks in advance (3 Replies)
Discussion started by: ken6503
3 Replies

3. UNIX for Dummies Questions & Answers

Divide a numerical data column by a variable

Hello, I have two files, f1 and f2. f1 has 5 columns like so: a b c d 154 e f g h 365 ..... f2 has two columns, the first column contains the name of the above file and second column contains a constant which is to be used for division. e.g. file1 56 I want to divide the 5th... (2 Replies)
Discussion started by: Gussifinknottle
2 Replies

4. Shell Programming and Scripting

Check null values column

hi, I had a small question.I had a file from which i need to extract data. I have written the below script to check if the file exists and if it exists extract requierd columns from the file. IFILE=/home/home01/Report_1.csv OFILE=/home/home01/name.csv.out1 if #Checks if file exists... (1 Reply)
Discussion started by: Vivekit82
1 Replies

5. Shell Programming and Scripting

Divide data with specific column values into separate files

hello! i need a little help from you :) ... i need to split a file into separate files depending on two conditions using scripting. The file has no delimiters. The conditions are col 17 = "P" and col 81 = "*", this will go to one output file; col 17 = "R" and col 81 = " ". Here is an example. ... (3 Replies)
Discussion started by: chanclitas
3 Replies

6. Shell Programming and Scripting

Remove null in certain column

Hi, gurus, I need remove lines in a file which contains null value in certain column eg. 123, ,abc,def,cde 234,abc,cde,def 456,cde, ,bcd in this file I need remove lines which second column contains null. expected result: 234,abc,cde,def 456,cde, ,bcd :wall: Thanks in advance (2 Replies)
Discussion started by: ken002
2 Replies

7. Shell Programming and Scripting

how to find null column

Hi, everyone I have a requirement as following: source file 1, abc, def, caaa 2, , cde, aaa 3, bcd, , adefefg I need find columns which contains null value, in above example, I need get two rows 2, , cde, aaa 3, bcd, , adefefg anybody has idea how to achive this ... (5 Replies)
Discussion started by: ken002
5 Replies

8. Shell Programming and Scripting

awk count characters, sum, and divide by another column

Hi All, I am another biologist attempting to parse a large txt file containing several million lines like: tucosnp 56762 T Y 228 228 60 23 .CcCcc,,..c.c,cc,,.C... What I need to do is get the frequency of periods (.) plus commas (,) in column 9, and populate this number into another... (1 Reply)
Discussion started by: peromhc
1 Replies

9. UNIX for Dummies Questions & Answers

Check for null values in a column

Hi All, I have a file with 10 columns and get the required data for nine columns properly except 8th. In 8th column i have both NULL and NON NULL values...i.e certain records have values for all the columns including 8th column and certain records have 8th column as NULL.My requisite is,without... (20 Replies)
Discussion started by: ganesh_248
20 Replies

10. Shell Programming and Scripting

How to check Null values in a file column by column if columns are Not NULLs

Hi All, I have a table with 10 columns. Some columns(2nd,4th,5th,7th,8th and 10th) are Not Null columns. I'll get a tab-delimited file and want to check col by col and generate seperate error code for each col eg:102 if 2nd col value is NULL and 104 if 4th col value is NULL so on... I am a... (7 Replies)
Discussion started by: Mandab
7 Replies
Login or Register to Ask a Question