DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
orginal6
Participant



Joined: 25 May 2019
Posts: 2

Points: 33

Post Posted: Mon May 27, 2019 9:16 pm Reply with quote    Back to top    

DataStage® Release: 9x
Job Type: Parallel
OS: Windows
Looking for guidance on how to process a file that is in the format below. I was having trouble using the pivot stage and wanted to see if there were any other ideas.
Account#1 Account #2 Account#3
Account Type Master Checking Savings
Account ID 123 987 654
Beginning Balance 1000 550 450
Indirect Activity 5 25 55
Expenses 12 12 12
Transfers Out 0 0 0
Ending Balance 1005 562 505

Target File
Account ID Ending Balance Expenses
123 1005 12
987 562 12
654 505 12

I know that my source file will always have 3 accounts, that is fixed and won't be dynamic.
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 367

Points: 3806

Post Posted: Tue May 28, 2019 10:37 pm Reply with quote    Back to top    

the first line is inconsistent (it has the column label 3 times, the others only once). Is that correct?

what do you want from it... 3 records? One big record with _1 _2 _3 on the columns?
Rate this response:  
Not yet rated
orginal6
Participant



Joined: 25 May 2019
Posts: 2

Points: 33

Post Posted: Wed May 29, 2019 1:35 am Reply with quote    Back to top    

That's correct the data is stored in the columns.

I reformatted the source file for better reference. I never worked with data in this format. I'm looking for suggestions on the best way to process the data.

Code:
                              Account#1   Account #2    Account#3
Account Type                  Master      Checking      Savings
Account ID                    123         987           654
Beginning Balance             1000        550           450
Indirect Activity             5           25            55
Expenses                      12          12            12
Transfers Out                 0           0             0
Ending Balance                1005        562           505

Target File
Account ID| Ending Balance |Expenses
123 |1005 |12
987 |562 |12
654 |505 |12
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42959
Location: Denver, CO
Points: 221538

Post Posted: Wed May 29, 2019 2:11 am Reply with quote    Back to top    

And I reformatted the source file for more better reference. Wink

The [code] tags preserve the "extra" whitespace that the forum software automatically removes. You need to use them and then edit/preview/edit/preview/lather/rinse/repeat until things actually line up in the final version.

_________________
-craig

Now I've got that feeling once again, I can't explain; you would not understand
this is now who I am. I have become comfortably numb.
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 367

Points: 3806

Post Posted: Wed May 29, 2019 9:18 pm Reply with quote    Back to top    

it looks to me like you need to add a column to your data for account number, and once you have that, a pivot should work?

If you don't want to do a pivot or can't get it to work, you can parse it any number of ways. Datastage can do it with transformers and funnels and such, but it is clunky ... stage variables, stamping a key to each line until the keys change, and reassemble based off the keys. You would probably have to turn off parallel processing for that bit. Or a routine. Or an external program that you call.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54518
Location: Sydney, Australia
Points: 295634

Post Posted: Thu Jun 13, 2019 1:47 am Reply with quote    Back to top    

If there are always these six rows, you can derive the three output columns based on the input row count. You need the first, sixth and fourth rows from each set. Be careful to partition your dat ...

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours