Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Merging of rows

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merging of rows
# 15  
Old 01-05-2009
Sample Data
Hi,

Please find the sample data. The file contains 3 rows. First row breaks into many lines, row 2nd and 3rd are without any line breaks. So, when the script runs, row starts with word 'Type|', should not merge with previous row but if not then should merge with previous line.

Row 1

Type|GenericSalesTicket|TradeID|103242224A|SystemID|179461266|SourceSystem|RMS|SourceSystemBook|GENERIC|B ook|GENERIC|BookBranchParent|Sales|Version|5|LatestVersionYN|Y|Counterparty|DBOB|Status|Expired|IsLi veYN|N|DBLegalEntity|London|Notes|LDN - IRATE - TPF - cal events added|Salesperson|HUNDHX|LastUpdated|18 DEC 2008 18:31:40|Action|Expired|CounterpartySourcesystem|RMS|TradeDate|16 DEC 2008|ProductID|173898893|StructureID|103242224|DealCaptureSystem|RMSSales|CustomerSourceSystem|RMS|C ustomer|BMK/AUGS|ActionEffectiveDate|18 DEC 2008 00:00:00|BookingLocation|London|EnteredBy|GONZMANU|MarkupAmt|12000|MarkupCcy|EUR|MarkupLocation|Bran ch|AtRiskAmt|5000|AtRiskCcy|EUR|AtRiskLocation|Branch|PremiumCurrency|EUR|PremiumAmount|-12000|PremiumDate|18 DEC 2008|PremiumHasPaidYN|N|BookingDetails|079000/mihl
Client sells EURUSD
spot ref 1.3724
Leveraged Settlement is at SPOT
52 settlements, starting : see table
Dates see table

Each cashflow : notional 0.0500 million

Row 2
Type|CashBalance|TradeID|Funding CashBalance|SystemID|2126192|SourceSystem|RMS|SourceSystemBook|LNFEXOT|Book|LNFEXOT|BookBranchParent |Forward|Version|1652|LatestVersionYN|Y|Counterparty|FUNDING CASHBALANCE|Status|Open|IsLiveYN|Y|DBLegalEntity|London|LastUpdated|02 JAN 2009 05:04:48|Reason|TREASURY|Action|Funding|CounterpartySourcesystem|RMS|TradeDate|23 AUG 2002|ProductID|1194926|StructureID|0|DealCaptureSystem|RMS|CustomerSourceSystem|RMS|Customer|FUNDING CASHBALANCE|Currency|BND|Amount|161796.5913075|OpenAmount|0|ActionEffectiveDate|31 DEC 2008 00:00:00|SourceSystemProfitCentre|Funding|BookingLocation|London|EnteredBy|RMS_OWNER|SourceSystemID| 2126192|DealCaptureSystemID|2126192|DateCreated|23 AUG 2002 00:04:06|ProductVersion|1|TradeClassification|0|PublicationTime|01 Jan 2009 23:59:49

Row 3
Type|FxGeneralAccrualOpt|TradeID|103667640A|SystemID|180131956|SourceSystem|RMS|SourceSystemBook|EURGEN|B ook|EURGEN|BookBranchParent|Global|Version|5|LatestVersionYN|Y|Counterparty|PUTZ|Status|Open|IsLiveY N|Y|DBLegalEntity|London|Notes|Amended fixing ref to WMR 11 per David|Salesperson|WEISDAV|LastUpdated|29 DEC 2008 21:38:45|Action|Fixed|CounterpartySourcesystem|RMS|TradeDate|18 DEC 2008|ProductID|174561397|StructureID|103667640|DealCaptureSystem|FXPricer(Sales)|CustomerSourceSyste m|RMS|Customer|PUTZ|UnderCurrency|EUR|AccCurrency|USD|Notional|4000000|Strike|1.395|CP|C|PayoffType| O|ExpiryDate|26 MAR 2009|SettlementDate|30 MAR 2009|FixingReference|WMRSPOT11|FractionalNotionalSoFar|0.25|PayCurrency|USD|AccrualStartDate|18 DEC 2008|AccrualEndDate|26 MAR 2009|FixingHolidays|EUR,USD|WeightHolidays|EUR,USD|LastFixedDate|26 DEC 2008|BarrierStartDate|18 DEC 2008|BarrierEndDate|26 MAR 2009|MinNotionalLeverage|0|MaxNotionalLeverage|700|FixingDate|26 DEC 2008|FixingWeight|1|FixingValue|1.4054|FixingDate|28 JAN 2009|FixingWeight|1|FixingDate|25 FEB 2009|FixingWeight|1|FixingDate|26 MAR 2009|FixingWeight|1|AccrualRegionStartDate|18 DEC 2008|AccrualRegionEndDate|26 MAR 2009|UpperExtinguishingLevel|1.58|UpperResurrectingLevel|1.58|LowerResurrectingLevel|1.395|LowerExti nguishingLevel|0.00001|UpperNotionalLeverage|0|MidNotionalLeverage|1|LowerNotionalLeverage|7|ActionE ffectiveDate|26 DEC 2008 00:00:00|BookingLocation|London|EnteredBy|ROLLMIC|MarkupAmt|259220|MarkupCcy|EUR|MarkupLocation|Lond on|SourceSystemID|180131956|DealCaptureSystemID|180131956|DateCreated|19 DEC 2008 00:39:20|ProductVersion|2|ProductLegId|1|ReferenceSpot|1.4235|ReferenceForwardPoints|-0.003740976|ReferenceVolatility|24.23007467|ReferenceDepoRate|1.87784704|ReferenceDepoCurrency|USD|R eferenceDelta|-0.2971194766996|ReferenceDeltaCurrency|EUR|TradeExtraDetailID|0|TradeExtraDetailVersion|0|TradeClass ification|2|DealCaptureSalesUser|FXPRICERSALES_WEISDAV|FixingsAccruedSoFar|1|TotalFixings|4|AccruedN otionalSoFar|1000000|PublicationTime|29 Dec 2008 14:56:11

The Output Should be

Row 1

Type|GenericSalesTicket|TradeID|103242224A|SystemID|179461266|SourceSystem|RMS|SourceSystemBook|GENERIC|B ook|GENERIC|BookBranchParent|Sales|Version|5|LatestVersionYN|Y|Counterparty|DBOB|Status|Expired|IsLi veYN|N|DBLegalEntity|London|Notes|LDN - IRATE - TPF - cal events added|Salesperson|HUNDHX|LastUpdated|18 DEC 2008 18:31:40|Action|Expired|CounterpartySourcesystem|RMS|TradeDate|16 DEC 2008|ProductID|173898893|StructureID|103242224|DealCaptureSystem|RMSSales|CustomerSourceSystem|RMS|C ustomer|BMK/AUGS|ActionEffectiveDate|18 DEC 2008 00:00:00|BookingLocation|London|EnteredBy|GONZMANU|MarkupAmt|12000|MarkupCcy|EUR|MarkupLocation|Bran ch|AtRiskAmt|5000|AtRiskCcy|EUR|AtRiskLocation|Branch|PremiumCurrency|EUR|PremiumAmount|-12000|PremiumDate|18 DEC 2008|PremiumHasPaidYN|N|BookingDetails|079000/mihl Client sells EURUSD spot ref 1.3724 Leveraged Settlement is at SPOT 52 settlements, starting : see table Dates see table Each cashflow : notional USD 0.0500 million

Row 2

Type|CashBalance|TradeID|Funding CashBalance|SystemID|2126192|SourceSystem|RMS|SourceSystemBook|LNFEXOT|Book|LNFEXOT|BookBranchParent |Forward|Version|1652|LatestVersionYN|Y|Counterparty|FUNDING CASHBALANCE|Status|Open|IsLiveYN|Y|DBLegalEntity|London|LastUpdated|02 JAN 2009 05:04:48|Reason|TREASURY|Action|Funding|CounterpartySourcesystem|RMS|TradeDate|23 AUG 2002|ProductID|1194926|StructureID|0|DealCaptureSystem|RMS|CustomerSourceSystem|RMS|Customer|FUNDING CASHBALANCE|Currency|BND|Amount|161796.5913075|OpenAmount|0|ActionEffectiveDate|31 DEC 2008 00:00:00|SourceSystemProfitCentre|Funding|BookingLocation|London|EnteredBy|RMS_OWNER|SourceSystemID| 2126192|DealCaptureSystemID|2126192|DateCreated|23 AUG 2002 00:04:06|ProductVersion|1|TradeClassification|0|PublicationTime|01 Jan 2009 23:59:49

Row 3
Type|FxGeneralAccrualOpt|TradeID|103667640A|SystemID|180131956|SourceSystem|RMS|SourceSystemBook|EURGEN|B ook|EURGEN|BookBranchParent|Global|Version|4|LatestVersionYN|N|Counterparty|PUTZ|Status|Open|IsLiveY N|Y|DBLegalEntity|London|Notes|Amended fixing ref to WMR 11 per David|Salesperson|WEISDAV|LastUpdated|29 DEC 2008 21:37:22|Action|NonEconomic|CounterpartySourcesystem|RMS|TradeDate|18 DEC 2008|ProductID|174561397|StructureID|103667640|DealCaptureSystem|FXPricer(Sales)|CustomerSourceSyste m|RMS|Customer|PUTZ|UnderCurrency|EUR|AccCurrency|USD|Notional|4000000|Strike|1.395|CP|C|PayoffType| O|ExpiryDate|26 MAR 2009|SettlementDate|30 MAR 2009|FixingReference|WMRSPOT11|PayCurrency|USD|AccrualStartDate|18 DEC 2008|AccrualEndDate|26 MAR 2009|FixingHolidays|EUR,USD|WeightHolidays|EUR,USD|BarrierStartDate|18 DEC 2008|BarrierEndDate|26 MAR 2009|MinNotionalLeverage|0|MaxNotionalLeverage|700|FixingDate|26 DEC 2008|FixingWeight|1|FixingDate|28 JAN 2009|FixingWeight|1|FixingDate|25 FEB 2009|FixingWeight|1|FixingDate|26 MAR 2009|FixingWeight|1|AccrualRegionStartDate|18 DEC 2008|AccrualRegionEndDate|26 MAR 2009|UpperExtinguishingLevel|1.58|UpperResurrectingLevel|1.58|LowerResurrectingLevel|1.395|LowerExti nguishingLevel|0.00001|UpperNotionalLeverage|0|MidNotionalLeverage|1|LowerNotionalLeverage|7|ActionE ffectiveDate|29 DEC 2008 00:00:00|BookingLocation|London|EnteredBy|ROLLMIC|MarkupAmt|259220|MarkupCcy|EUR|MarkupLocation|Lond on|SourceSystemID|180131956|DealCaptureSystemID|180131956|DateCreated|19 DEC 2008 00:39:20|ProductVersion|2|ProductLegId|1|ReferenceSpot|1.4235|ReferenceForwardPoints|-0.003740976|ReferenceVolatility|24.23007467|ReferenceDepoRate|1.87784704|ReferenceDepoCurrency|USD|R eferenceDelta|-0.2971194766996|ReferenceDeltaCurrency|EUR|TradeExtraDetailID|0|TradeExtraDetailVersion|0|TradeClass ification|2|DealCaptureSalesUser|FXPRICERSALES_WEISDAV|PublicationTime|29 Dec 2008 14:56:11
# 16  
Old 01-05-2009
Try this:

Code:
perl -nle'
  print $x and undef $x 
    if $x and /^Type/ or eof;
   ($x .= $_) =~ tr/\n/ /s;
  ' infile

# 17  
Old 01-05-2009
How to Identify of Merged rows
How to identify which rows got Merged ?

It's not working if I modify the script as below

($x .= $_) =~ tr/\n/LINE_BREAK/s;
# 18  
Old 01-05-2009
Actually tr is not needed because of the -l switch.

Try this:

Code:
perl -nle'
  print $x, $f > 1 ? "_FIXED_" : "" 
    and ($x, $f) = undef 
    if $x and /^Type/ or eof;
  $f++ if $x .= " ". $_ and !/^$/
  ' infile

You may add another linefeed if you need an extra blank line between the records.
Last edited by radoulov; 01-05-2009 at 04:16 PM..
# 19  
Old 01-06-2009
Space issue
Hi,
if I execute the script, there will be one space(Smilie) at the begaining of every line, the word '_FIXED_' comes only at the end of file but not where the line was break (_FIXED_) .

Row 1

SmilieType|GenericSalesTicket|TradeID|103242224A|SystemID|179461266|SourceSystem|RMS|SourceSystemBook|GENERIC|B ook|GENERIC|BookBranchParent|Sales|Version|5|LatestVersionYN|Y|Counterparty|DBOB|Status|Expired|IsLi veYN|N|DBLegalEntity|London|Notes|LDN - IRATE - TPF - cal events added|Salesperson|HUNDHX|LastUpdated|18 DEC 2008 18:31:40|Action|Expired|CounterpartySourcesystem|RMS|TradeDate|16 DEC 2008|ProductID|173898893|StructureID|103242224|DealCaptureSystem|RMSSales|CustomerSourceSystem|RMS|C ustomer|BMK/AUGS|ActionEffectiveDate|18 DEC 2008 00:00:00|BookingLocation|London|EnteredBy|GONZMANU|MarkupAmt|12000|MarkupCcy|EUR|MarkupLocation|Bran ch|AtRiskAmt|5000|AtRiskCcy|EUR|AtRiskLocation|Branch|PremiumCurrency|EUR|PremiumAmount|-12000|PremiumDate|18 DEC 2008|PremiumHasPaidYN|N|BookingDetails|079000/mihl _FIXED_ Client sells EURUSD _FIXED_ spot ref 1.3724 _FIXED_ Leveraged Settlement is at SPOT _FIXED_ 52 settlements, starting : see table _FIXED_ Dates see table _FIXED_ Each cashflow : notional USD 0.0500 million_FIXED_
# 20  
Old 01-06-2009
Code:
sed '/^Type/{:a;N;/\nType/!{s/\n/ /;ba};s/\n/&&/}'

# 21  
Old 01-06-2009
no luck
Hi Radoulov,

Could you please help me to resolve this issue?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Merging rows based on same ID in First column.

Hellow, I have a tab-delimited file with 3 columns : BINPACKER.13259.1.p2 SSF48239 BINPACKER.13259.1.p2 PF13243 BINPACKER.13259.1.p2 G3DSA:1.50.10.20 BINPACKER.13259.2.p2 SSF48239 BINPACKER.13259.2.p2 PF13243 BINPACKER.13259.2.p2 G3DSA:1.50.10.20... (7 Replies)
Discussion started by: anjaliANJALI
7 Replies

2. Shell Programming and Scripting

Merging rows after matching a pattern

Hi All, I have the below file where I want the lines to merged based on a pattern. AFTER CMMILAOJ CMMILAAJ AFTER CMDROPEJ CMMIMVIJ CMMIRNTJ CMMIRNRJ CMMIRNWJ CMMIRNAJ CMMIRNDJ AFTER CMMIRNTJ CMMIRNRJ CMMIRNWJ (4 Replies)
Discussion started by: varun22486
4 Replies

3. Shell Programming and Scripting

Moving or copying first rows and last rows into another file

Hi I would like to move the first 1000 rows of my file into an output file and then move the last 1000 rows into another output file. Any help would be great Thanks (6 Replies)
Discussion started by: kylle345
6 Replies

4. Shell Programming and Scripting

Merging rows in awk

Hello, I have a data format as follows: Ind1 0 1 2 Ind1 0 2 1 Ind2 1 1 0 Ind2 2 2 0 I want to use AWK to have this output: Ind1 00 12 21 Ind2 12 12 00 That is to merge each two rows with the same row names. Thank you very much in advance for your help. (8 Replies)
Discussion started by: Homa
8 Replies

5. Shell Programming and Scripting

Merging rows using two common rows.

Hi.. My requirement is simple but unable to get that.. File 1 : 3 415 A G 4 421 G . 39 421 G A 2 421 G A,C 41 427 A . 4 427 A C 42 436 G . 3 436 G C 43 445 C . 2 445 C T 41 447 A . Output (4 Replies)
Discussion started by: empyrean
4 Replies

6. UNIX Desktop Questions & Answers

merging files and add missing rows

hello all, I have files that have a specific way for naming the first column they are make of five names in Pattern of 3 Y = (no case sensitive) so the files are names $Y-$Y-$Y or $X-$Y-$Z depending how we look they only exist of the pattern exist now I want to create a file from them that... (9 Replies)
Discussion started by: A-V
9 Replies

7. UNIX for Dummies Questions & Answers

merging rows into new file based on rows and first column

I have 2 files, file01= 7 columns, row unknown (but few) file02= 7 columns, row unknown (but many) now I want to create an output with the first field that is shared in both of them and then subtract the results from the rest of the fields and print there e.g. file 01 James|0|50|25|10|50|30... (1 Reply)
Discussion started by: A-V
1 Replies

8. Shell Programming and Scripting

Merging rows with same column 1 value

I have the following space-delimited input: 1 11.785710 117.857100 1 15 150 1 20 200 1 25 250 3 2.142855 21.428550 3 25 250 22 1.071435 10.714350 The first field is the ID number, the second field is the percentage of the total points that the person has and the third column is the number... (3 Replies)
Discussion started by: mdlloyd7
3 Replies

9. Shell Programming and Scripting

Split single rows to multiple rows ..

Hi pls help me out to short out this problem rm PAB113_011.out rm: PAB113_011.out: override protection 644 (yes/no)? n If i give y it remove the file. But i added the rm command as a part of ksh file and i tried to remove the file. Its not removing and the the file prompting as... (7 Replies)
Discussion started by: sri_aue
7 Replies

10. Shell Programming and Scripting

Deleting specific rows in large files having rows greater than 100000

Hi Guys, I need help in modifying a large text file containing more than 1-2 lakh rows of data using unix commands. I am quite new to the unix language the text file contains data in a pipe delimited format sdfsdfs sdfsdfsd START_ROW sdfsd|sdfsdfsd|sdfsdfasdf|sdfsadf|sdfasdf... (9 Replies)
Discussion started by: manish2009
9 Replies
Login or Register to Ask a Question