Login or Register to Ask a Question and Join Our Community


Linux

Pivoting data with awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Operating Systems Linux Pivoting data with awk
# 1  
Old 04-27-2016
Pivoting data with awk
Hi Friends,
I need to pivot data .
Below is my source data

Source Data
Code:
PK PRTY_KEY_ID PRTY_SUB_KEY_ID KEY_COL_VAL_TX MTCH_CNFDNCE_RATE
007824822 428844791 1 #Jemmy#Pom#600 Kearsarge Way 100
007824822 429283974 1 #Jemmy#Pom#120 Broadway 100
007824822 429739103 1 #Jemmy#Pom#600 Keae Way#757 90
007824822 429834732 1 #Jemmy#Pom###10128 80
007824822 429840574 1 #Jemmy#Pom###10271 80
007824822 429845694 1 #Jemmy#Pombriant###03801 80
369540998 428511642 1 #C Ann#Gracer#7320 Mundy Ln 100
369540998 429296516 1 #C Ann#Gracer#2150 Linda Dr 100
369540998 429855219 1 #C#Gracer###48356 80

===================================
I need output like below

Code:
PK PRTY_KEY_ID SUB_KEY MTCH_PRTY_KEY_ID KEY_COL_VAL_TX 
007824822 428844791 1 429739103 #Jemmy#Pom#600 Keae Way#757
007824822 428844791 1 429834732 #Jemmy#Pom###10128 
007824822 428844791 1 429840574 #Jemmy#Pom###10271 
007824822 428844791 1 429845694 #Jemmy#Pombriant###03801 
007824822 429840574 1 429739103 #Jemmy#Pom#600 Keae Way#757
007824822 429840574 1 429834732 #Jemmy#Pom###10128 
007824822 429840574 1 429840574 #Jemmy#Pom###10271 
007824822 429840574 1 429845694 #Jemmy#Pombriant###03801 
369540998 428511642 1 429855219 #C#Gracer###48356
369540998 429296516 1 429855219 #C#Gracer###48356

===================
For each 100 percent PRTY_KEY_ID for same PK, I need to list down its all 90 and 80's PRTY_KEY_ID
So for PK = 007824822, FOR first 100 % PRTY_KEY_ID (428844791), I will need four rows(3 FOR 80 and 1 for 90)
for PK =007824822, , For next 100% PRTY_KEY_ID (429840574),I will need four rows(3 FOR 80 and 1 for 90)
Thne next PK and so on

===============
Need your help

Thanks in advance.
Moderator's Comments:
Mod Comment Please use CODE tags when displaying sample input, sample output, and code segments as required by forum rules.
Last edited by Don Cragun; 04-27-2016 at 06:30 AM.. Reason: Add CODE tags.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Pivoting data based on a header field

Hi Team, Could you please help me with the below scenario. I have a file which is in the below format. Zipcode,001,001f,002,002f,003,003f,004,004f,005,005f,006,006f,007,007f 0050, ,0, ,0, ,0, ,1,*,7, ,7, ,7 0060, ,0, ,0, ,7, ,0,*,7, ,0, ,0 Would need the output as below. First field... (1 Reply)
Discussion started by: saj
1 Replies

2. UNIX for Beginners Questions & Answers

Pivoting with awk

My input file(inputfile.txt): Job name.... EXTDPL_1 Created on.. 2018-11-19 14:00:00 Modified on. 2018-11-22 11:54:46 Job name.... EXTDPL_2 Created on.. 2018-11-21 12:31:27 Modified on. 2018-11-21 12:35:28 2 records listed. >Q expected output: Job name Created... (3 Replies)
Discussion started by: JSKOBS
3 Replies

3. UNIX for Beginners Questions & Answers

Pivoting values from column to rows

I/P: I/P: 2017/01/01 a 10 2017/01/01 b 20 2017/01/01 c 40 2017/02/01 a 10 2017/02/01 b 20 2017/02/01 c 30 O/P: a b c 2017/01/01 10 20 40 2017/02/01 10 20 30 (18 Replies)
Discussion started by: Booo
18 Replies

4. Homework & Coursework Questions

Help with pivoting table shell script

input file txt file 2000 1 name 2000 2 addr 2000 3 phone 2000 4 email 1000 1 name 1000 2 addr 1000 3 phone 1000 4 email 3000 1 name 3000 2 addr 3000 ... (4 Replies)
Discussion started by: senmatrix
4 Replies

5. Shell Programming and Scripting

Pivoting using shell scripts

Hi , Please any one help using shell scripts achieve the below output(pivoting on top_cd i mean type code values will come as individual columns and fixed amount is value for that .Any help would be greate tx_id tx_amt typ_cd fixed_dis_amt 100 200 mc ... (3 Replies)
Discussion started by: mohan705
3 Replies

6. Shell Programming and Scripting

Pivoting the data

Hello Unix guys, I have the following 4 column data, which is a output of db2 select query: Need to pivot the data. sample Input: Year Month Country Counts 2012 Aug Canada 114 2012 Aug USA 92 2012 Aug Mexico 3 2012 Aug ... (3 Replies)
Discussion started by: karumudi7
3 Replies

7. Shell Programming and Scripting

Vertical And Horizontal Pivoting

Hi All, My Input data is: A=1 B=2 My desired Output should be: A|B 1|2 Thanks in advance... (3 Replies)
Discussion started by: kmsekhar
3 Replies

8. Shell Programming and Scripting

Help with pivoting

Hi, I need help on how to pivot the data in UNIX. I have one input file in which The facts(FACT1,FACT2..) and PERIOD(JAN,FEB..) are columns.I need to pivot the data. For Exampe The input file and output file looks like below. Could you please help with this using awk:). INPUT: ====== ... (1 Reply)
Discussion started by: Gayathricheruku
1 Replies

9. Shell Programming and Scripting

Pivoting Dynamic rows into columns

Original file we are getting ....... Item Period Amt P1 106 1000 P1 206 1500 P1 106 2000 P2 256 5800 P2 650 7500 My output should be like this Item 106 206 256 650 ............ P1 1000 1500 0 ... (1 Reply)
Discussion started by: dprakash
1 Replies

10. UNIX for Dummies Questions & Answers

Pivoting a Single column

Hi, Input ID|Name 1|a,b,c 2|d,e,f,g I would like to get output in the following format. Output ID|NAME 1|a 1|b 1|c 2|d 2|e 2|f 2|g (2 Replies)
Discussion started by: deepakwins
2 Replies
Login or Register to Ask a Question