AnsweredAssumed Answered

Combine multiple related rows of data positioned file into a single row

Question asked by sujith.59171732 on Jun 25, 2018
Latest reply on Jun 25, 2018 by SaiKumarGoud Mallari

Hello All, 

 

I have got struck on an interesting problem , hope someone can give me a helping hand. 

 

My Input Source Data Positioned Flat File looks below : 

 

It is sample ticketing file with multiple tickets in it ,TYPE0  row represents the starting of a new ticket,until the next TYPE0 encounters all details are of that ticket.

Each TYPE (TYPE0, TYPE0GRADES, TYPE2) are having different data positioned layout, so separate data positioned flat file profile is required to read those values.

At the end my aim to read one-by-one ticket (including its different Type details) which are set as different data positioned layout in different rows and combines and maps into a single row (& multiple columns) of a table.So that I will have one row representing one ticket with all of its details in multiple columns in my target database table.

Hope it clarifies the requirement. Thanks for your help in advance..!!

 

You can consider the ticket number as the orange colored value , which will be unique & not null (Primary Key)

 

TYPE0 0033083648R18060718060743907 01 T HGF 0 FFF 1057.18 87140 26320 60820 0.0000 0.00 0.00000 0.0000018060708:58 118060709:05 1Y 0 7 Y FI0033083648 001001A 0033083648 01
TYPE0GRADES: 16.90M 56.80T 0.50F 2.10A 0.00MU 0.00S 0.00H
TYPE2 01SPA
TYPE2 02KT
TYPE2 05MORN 170414
TYPE1 xx xx43907 1086.07 1057.18 1057.18 0.00000 0.00000
TYPERATES: 2.6600%gM 1 0.0000cgT 1 0.0000cnF 1 0.0000cnA 1 0.0000 S 1

TYPE0 0030013414R1806071806071109 01 T40099 HELL EEE 922.14 80180 28540 51640 0.0000 0.00 0.00000 0.0000018060710:28 K18060710:18 1Y 0 8 Y IO0030013414 001001A 0030013414 01
TYPE0GRADES: 14.60M 57.70T 0.50F 2.10A 0.00MU 0.00S 0.00H
TYPE2 01chelsea
TYPE2 05CORN 170414
TYPE1 xx xx1109 922.14 922.14 922.14 0.00000 0.00000
TYPE1RATES: 0.0000cgM 5 0.0000cnT 5 0.0000%nF 5 0.0000cnA 5 0.0000 S 5

Outcomes