2

I have a file having the following records (pipe delimited):

SK200-10|ALBUMIN-SK|INS|SEKURE ALBUMIN 2 X 65ML|REAG-IVD|SEKI-PEI|90|14IAG|KIDNEY/LIVER|MD|NA|0|U|MFGPRO|15-May-2014|15-May-2014|MFGPRO|CAPPROD
P/G-PPR|NA-INS|INS|GEWINDEFLASCHE +DECKEL ROT|NON-REAG|ZINSSER|0|NA|NA|NA|NON-REAG|0|U|DM|3-Jun-2014|3-Jun-2014|MFGPRO|UKPROD
P/G-PPR|NA-INS|INS|GEWINDEFLASCHE +DECKEL ROT|NON-REAG|ZINSSER|0|NA|NA|NA|NON-REAG|0|U|DM|3-Jun-2014|3-Jun-2014|DM|QADDB
2898|OTHER-RT|RT|PRINTED MEMBRANE, ADENOVIRUS|NON-REAG|SEKI-SD|60|NA|NA|NA|NA|0|U|MFGPRO|24-Apr-2013|24-Apr-2013|MFGPRO|PACEPROD

In these pipe delimited records: The first value is the item number. The 13th value is the status flag. The last value is the database name.

Check for duplicate records based on the Item number from the UKPROD and QADDB database. If a duplicate is found: Change the value of the status flag (13th value) to 'X' from the QADDB database.

For example, in the above 4 records:

The item number is same for the 2nd and the 3rd record. I then check if they exist between the UKPROD and the QADDB databases. If yes, the I mark the status flag as 'X' for the record from QADDB database.

  • Does the UKPROD entry for a given item number always precede any possible QADDB entry? or is the order indeterminate? – steeldriver Jun 30 '14 at 12:01
  • If I am understanding your question, yes the order of the item records is indeterminate. we have nightly scripts running which consolidates these item records from 4 databases. and we have to mark status flag of the records from qaddb which matches with teh records from ukprod. – dhotearp Jul 1 '14 at 8:56
1

If the order of the UKPROD and QADDB entries for each product ID is not fixed, then I think you will need to read and process the file twice - on the first pass, construct a lookup table of all the products (field #1) in the UKPROD database, and on the second pass check each product that's shown against QADDB for presence in UKPROD. For example, one possible solution in awk might be

awk -F\| '
BEGIN{OFS=FS}; 
NR==FNR {if ($NF=="UKPROD") ukprod[$1]++; next} 
NR!=FNR {if (ukprod[$1] && $NF=="QADDB") $13="X"} 
1' yourfile yourfile

but likely there are better implementations. If UKPROD and QADDB refer to actual databases, then it might be more efficient to do an actual database query instead of test-processing the flat file.

  • Thanks much. I will look into this and see if it works for me.Unfortunately, we cannot do an actual database query because the qaddb and ukprod are on different platforms. so the only way is scripting. – dhotearp Jul 2 '14 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.