I have a Sequential file that consists of a Branch Number followed by 10 occurences of an Account number (if there are not 10 valid Account numbers then the remaining ones are set to 00000000), all related to the initial Branch Number.
So 400100 is the Branch Number and 00166448, 00154568, 00142268 are the related accounts.
What I am wanting to do is cut the file down so that instead of having all 10 accounts on 1 line against the Branch Number, I am wanting it to appear as below
a) You can use a pivot stage on the data, and then remove all 00000000 valued rows.
b) write the data to a Hash file with a key being the branch number and a multivalue column containing the account string, with each account separated by a @VM. Then read the hash file and normalize on the account number and you will get one row per account number.
Hi, You could also use a pivot stage. Key on your Branch Number, and if your account numbers are always 8 characters long, then create 10 output columns using substrings to represent the account codes, which will become your 10 output rows for each Branch Number.
Last edited by garthmac on Tue Mar 15, 2005 6:08 am, edited 1 time in total.
a) You can use a pivot stage on the data, and then remove all 00000000 valued rows.
b) write the data to a Hash file with a key being the branch number and a multivalue column containing the account string, with each account separated by a @VM. Then read the hash file and normalize on the account number and you will get one row per account number.
You can use the FOLD of FMT function to split the line into a delimited string easily. Then convert the delimiter character (@FM from FOLD, @TM from FMT) into @VM using CONVERT or RAISE or LOWER.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
If you are sure about the sequential format, you could also take the first 8 digits and put it into a Stage Variable and then you could take the sequential counts and append it to the Staged Variable taken onto each column. You will get the results in different columns.
Ex.
Staged Variable - BranchNum as DSLink3.Field001 [1,8]
Column1 as BranchNum
Column2 as BranchNum : DSLink3.Field001 [9,6]
Column3 as BranchNum : DSLink3.Field001 [14,6] etc
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
The pivot stage will work in this instance as you are pivoting a wide record into a set of narrow records. You may find this is the simplest option.
You can also do it in a transformer calling a routine that turns your single row into up to ten rows. Have a look at the FAQ explaining how to insert end of record markets into text strings. This is only suitable if you are writing to a sequential file, it will write out a single line which will be written to the sequential file as multiple lines.
</a>