[Solved] missing date in unix

rabindratech · September 28, 2012, 7:50am

i have a file with below contents

Mg_Message_count,1-Aug-12,46
Mg_Message_count,2-Aug-12,48
Mg_Message_count,3-Aug-12,48
Mg_Message_count,4-Aug-12,48
Mg_Message_count,5-Aug-12,48
Mg_Message_count,6-Aug-12,48
Mg_Message_count,7-Aug-12,42
Mg_Message_count,20-Aug-12,24
Mg_Message_count,21-Aug-12,24
Mg_Message_count,22-Aug-12,24
Mg_Message_count,23-Aug-12,24
Mg_Message_count,24-Aug-12,24
Mg_Message_count,25-Aug-12,24

i want to know missing date 8-sept-12 to 19-sept-12 need display 0
otherwise display specific count
o/p
-------------------------------------

Mg_Message_count,1-Aug-12,46
Mg_Message_count,2-Aug-12,48
Mg_Message_count,3-Aug-12,48
Mg_Message_count,4-Aug-12,48
Mg_Message_count,5-Aug-12,48
Mg_Message_count,6-Aug-12,48
Mg_Message_count,7-Aug-12,42
Mg_Message_count,8-Aug-12,0
Mg_Message_count,9-Aug-12,0
Mg_Message_count,10-Aug-12,0
Mg_Message_count,11-Aug-12,0
Mg_Message_count,12-Aug-12,0
Mg_Message_count,13-Aug-12,0
Mg_Message_count,14-Aug-12,0
Mg_Message_count,15-Aug-12,0
Mg_Message_count,16-Aug-12,0
Mg_Message_count,17-Aug-12,0
Mg_Message_count,18-Aug-12,0
Mg_Message_count,19-Aug-12,0
Mg_Message_count,20-Aug-12,24
Mg_Message_count,21-Aug-12,24
Mg_Message_count,22-Aug-12,24
Mg_Message_count,23-Aug-12,24
Mg_Message_count,24-Aug-12,24
Mg_Message_count,25-Aug-12,24

quickly reply must be appriciated

pamu · September 28, 2012, 8:24am

try this...

awk -F "[-,]" '{if(a){if(($2-a) > 1){p=$1",";q="-"$3"-"$4",0";a++;for(i=a;i<=$2;i++){a++;print p""i""q;}}else{a=$2;print $0}}else{a=$2;print $0}}' file

rabindratech · September 30, 2012, 1:39am

not giving correct output for example
Mg_Message_count,8-Aug-12,0 Mg_Message_count,9-Aug-12,0 Mg_Message_count,10-Aug-12,0 Mg_Message_count,11-Aug-12,0 Mg_Message_count,12-Aug-12,0 Mg_Message_count,13-Aug-12,0 Mg_Message_count,14-Aug-12,0 Mg_Message_count,15-Aug-12,0 Mg_Message_count,16-Aug-12,0 Mg_Message_count,17-Aug-12,0 Mg_Message_count,18-Aug-12,0 Mg_Message_count,19-Aug-12,0

those are not available in my file hence for those date will be 0
---------------
Logic
if date available then display
(for example:Mg_Message_count,07-Aug-12,42)
else display count 0
for example:Mg_Message_count,11-Aug-12,0

---------- Post updated at 12:39 AM ---------- Previous update was at 12:27 AM ----------

Please let me you have any doubt

pamu · September 30, 2012, 3:38am

see this..

$ cat file
Mg_Message_count,1-Aug-12,46
Mg_Message_count,2-Aug-12,48
Mg_Message_count,3-Aug-12,48
Mg_Message_count,4-Aug-12,48
Mg_Message_count,5-Aug-12,48
Mg_Message_count,6-Aug-12,48
Mg_Message_count,7-Aug-12,42
Mg_Message_count,20-Aug-12,24
Mg_Message_count,21-Aug-12,24
Mg_Message_count,22-Aug-12,24
Mg_Message_count,23-Aug-12,24
Mg_Message_count,24-Aug-12,24
Mg_Message_count,25-Aug-12,24

$ awk -F "[-,]" '{if(a){if(($2-a) > 1){p=$1",";q="-"$3"-"$4",0";a++;for(i=a;i<$2;i++){a++;print p""i""q;}{print}}else{a=$2;print $0}}else{a=$2;print $0}}' file
Mg_Message_count,1-Aug-12,46
Mg_Message_count,2-Aug-12,48
Mg_Message_count,3-Aug-12,48
Mg_Message_count,4-Aug-12,48
Mg_Message_count,5-Aug-12,48
Mg_Message_count,6-Aug-12,48
Mg_Message_count,7-Aug-12,42
Mg_Message_count,8-Aug-12,0
Mg_Message_count,9-Aug-12,0
Mg_Message_count,10-Aug-12,0
Mg_Message_count,11-Aug-12,0
Mg_Message_count,12-Aug-12,0
Mg_Message_count,13-Aug-12,0
Mg_Message_count,14-Aug-12,0
Mg_Message_count,15-Aug-12,0
Mg_Message_count,16-Aug-12,0
Mg_Message_count,17-Aug-12,0
Mg_Message_count,18-Aug-12,0
Mg_Message_count,19-Aug-12,0
Mg_Message_count,20-Aug-12,24
Mg_Message_count,21-Aug-12,24
Mg_Message_count,22-Aug-12,24
Mg_Message_count,23-Aug-12,24
Mg_Message_count,24-Aug-12,24
Mg_Message_count,25-Aug-12,24

small correction in the code.. check above...

let me know if you still have any more conditions..? or you need another output..?

RudiC · September 30, 2012, 4:19am

Some questions around your problem: Does it span months? Does it need to cover month's end? If not, and being exactly as given in your post, try (using mawk on linux):

awk 'BEGIN{FS=OFS=","}
     {split($2,B,"-");
      while (B[1]!=++A) {h=sprintf ("%s-%s-%s", A,B[2],B[3]); print $1,h,"0"}
      A=B[1]
      print
     }
    ' file

rabindratech · October 1, 2012, 12:44am

this is giving the below output like
------------------

,235--,0
,236--,0
,237--,0
,238--,0
,239--,0

explanation:this is my input file as below given example. now thae date between 08-AUG-12 to 19-AUG-12 are missing/not available hence i need the count (Last field ) is 0 for dtd 08-AUG-12 to 19-AUG-12 else as it is.

Example:

MNP_Message_Gateway,Mg_Message_count,07-AUG-12,42
MNP_Message_Gateway,Mg_Message_count,20-AUG-12,24

pamu · October 1, 2012, 1:25am

Please look at your input file it is different than what you have given in the first post..

try this...

$ cat file
MNP_Message_Gateway,Mg_Message_count,07-AUG-12,42
MNP_Message_Gateway,Mg_Message_count,20-AUG-12,24

$ awk -F "[-,]" '{if(a){if(($3-a) > 1){p=$1","$2",";q="-"$4"-"$5",0";a++;for(i=a;i<$3;i++){a++;print p""i""q;}{print}}else{a=$3;print $0}}else{a=$3;print $0}}' file
MNP_Message_Gateway,Mg_Message_count,07-AUG-12,42
MNP_Message_Gateway,Mg_Message_count,8-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,9-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,10-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,11-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,12-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,13-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,14-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,15-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,16-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,17-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,18-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,19-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,20-AUG-12,24

rabindratech · October 2, 2012, 8:12am

Thanks, you are on track.This is giving output like by using below code

$ awk -F "[-,]" '{if(a){if(($3-a) > 1){p=$1","$2",";q="-"$4"-"$5",0";a++;for(i=a;i<$3;i++){a++;print p""i""q;}{print}}else{a=$3;print $0}}else{a=$3;print $0}}' file

SystemName,ComponentName,Date,Count
------------------------------------------------------

MNP_Message_Gateway,Mg_Message_count,07-AUG-12,42
MNP_Message_Gateway,Mg_Message_count,20-AUG-12,24

but not giving the below missing date(Which is not available in my file).which need to generate missing date automatically with 0 count.

MNP_Message_Gateway,Mg_Message_count,8-AUG-12,0--not available in file
MNP_Message_Gateway,Mg_Message_count,9-AUG-12,0--not available in file
MNP_Message_Gateway,Mg_Message_count,10-AUG-12,0--not available in file
MNP_Message_Gateway,Mg_Message_count,11-AUG-12,0--not available in file
MNP_Message_Gateway,Mg_Message_count,12-AUG-12,0--not available in file
MNP_Message_Gateway,Mg_Message_count,13-AUG-12,0--not available in file
MNP_Message_Gateway,Mg_Message_count,14-AUG-12,0--not available in file
MNP_Message_Gateway,Mg_Message_count,15-AUG-12,0--not available in file
--not available in file
MNP_Message_Gateway,Mg_Message_count,16-AUG-12,0--not available in file
MNP_Message_Gateway,Mg_Message_count,17-AUG-12,0--not available in file
MNP_Message_Gateway,Mg_Message_count,18-AUG-12,0--not available in file
MNP_Message_Gateway,Mg_Message_count,19-AUG-12,--not available in file--not available in file
----------------------------------------------------------
My O/P would be:
MNP_Message_Gateway,Mg_Message_count,07-AUG-12,42
MNP_Message_Gateway,Mg_Message_count,8-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,9-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,10-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,11-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,12-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,13-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,14-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,15-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,16-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,17-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,18-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,19-AUG-120
MNP_Message_Gateway,Mg_Message_count,20-AUG-12,24

Quick reply must be appriciated.Please let me know if you have any queries.

RudiC · October 2, 2012, 8:44am

Requesting a "quick reply" while waiting for a day or more with your input is a bit strange, as is the input being modified during the quest. Have you tried the suggestion in post #5? It worked for your original input sample. Adapt post #5 yourself to, or try this for, your modified input sample:

awk     'BEGIN{FS=OFS=","}
         {split($3,B,"-");
          if (NR==1) A=B[1]-1;
          while (B[1]!=++A) {h=sprintf ("%s-%s-%s", A,B[2],B[3]); print $1,$2,h,"0"}
          A=B[1]
          print
         }
        ' file

giving

MNP_Message_Gateway,Mg_Message_count,07-AUG-12,42
MNP_Message_Gateway,Mg_Message_count,8-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,9-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,10-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,11-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,12-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,13-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,14-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,15-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,16-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,17-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,18-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,19-AUG-12,0
MNP_Message_Gateway,Mg_Message_count,20-AUG-12,24

Are you sure the before last line in your desired output given above is what it should be?

You did not answer my questions a) does your problem span months? b) do month start/end need to be covered?

And, last but not least, is there a reason to constantly ignore moderators' requests to use code tags?

rabindratech · October 2, 2012, 10:17am

let say example
-----------------------------------------
systemdate,componentname,date,count
---------------------------------------
a,A_B,1-JAN-08,2
a,A_B,3-JAN-08,2
B,T_K,7-AUG-09,2
B,T_K,11-AUG-09,2
EXPLANATION:

Here correct the output will be below with missing date and available date
a,A_B,1-JAN-08,2
a,A_B,2-JAN-08,0
a,A_B,3-JAN-08,2
B,T_K,7-AUG-09,2
B,A_B,8-AUG-09,0
B,A_B,9-AUG-09,0
B,A_B,10-AUG-09,0
B,T_K,11-AUG-09,2

it need to print for entire year's day starting missing date and available date each system for e.g.
1) for a system will print 1-JAN-08 to till date
2) for B system will print 7-AUG-08 to till date
3)month start/end will be covered for each system
4) display till date for each system from min date available
Logic
if date available then diplay as it is
else (display from min date to till date available for the each system
for example:Mg_Message_count,07-Aug-12,42)
else
display count 0 for example:Mg_Message_count,7-Aug-12,0 ..to till date)

pamu · October 2, 2012, 10:41am

I think you have problem with file re-direction....

try this...

awk {} file > temp_file
mv temp_file file