DSXchange

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.

400100001664480015456800142268000000000000000000000000000000000000000000000000

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

40010000166448
40010000154568
40010000142268 etc, etc

Any ideas how this can be done ?

Cheers
Jez T

Use Multi-Valued files.

Not sure what multi-valued files are but think they are for Mainframe jobs whereas mine is a Server job.

JezT,

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.

Pivot Stage is a multiple row to multiple column mechanism and not vice-versa as required in this situation.

If you store in same table as

KeyCol, Ac1, Ac2, Ac3....

then you may be doing a UNION ALL such as

Select KelCol, Ac1 from pivottable
UNION ALL
Select KelCol, Ac2 from pivottable
UNION ALL
........
......

ArndW wrote:JezT,

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.

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

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.

You can write the result to another file with column value such as
Key : '-' : Col1 Key : '-' : Col2 etc and use newline as field delimiter.

When you read from this file, use '-' as field delimiter and define only 2 columns - one key and one non-key.

Thanks for all the advice. I have used a Pivot Stage and this has solved th problem nicely.

Thanks
Jez T

Glad to hear it. I think it was the easiest solution to get to grips with!