logic required for horizontal pivot
Moderators: chulett, rschirm, roy
logic required for horizontal pivot
Hi ,
I have sequential file with one record as source
Name cell# work# Home#
ABS 123 456 789
target output I required
Name Phone Description phone #
ABS cell 123
ABS work 456
ABS Home 789
Can anyone help on those.
I have sequential file with one record as source
Name cell# work# Home#
ABS 123 456 789
target output I required
Name Phone Description phone #
ABS cell 123
ABS work 456
ABS Home 789
Can anyone help on those.
in tranformer in am getting Name from input and pivoted data which contains Cell number,work number and home number.
can you help me on this that should i add three new columns in output side cell,home and work number and then map pivoted to them.
Can you please clarify me on this and if you give me example i really appreciate.
can you help me on this that should i add three new columns in output side cell,home and work number and then map pivoted to them.
Can you please clarify me on this and if you give me example i really appreciate.
The three new columns get added by a transformer before you pivot them so they can pivot together. So you have four columns coming in and you add three more:
Name: ABS
CellNum: 123
WorkNum: 456
HomeNum: 789
CellDesc: "Cell"
WorkDesc: "Work"
HomeDesc: "Home"
And then you pivot them together:
Name: Key field (repeats on all rows)
Description: CellDesc, WorkDesc, HomeDesc
PhoneNumber: CellNum, WorkNum, HomeNum
What you end up with post-pivot is:
Name, CellDesc, CellNum
Name, WorkDesc, WorkNum
Name, HomeDesc, HomeNum
Name: ABS
CellNum: 123
WorkNum: 456
HomeNum: 789
CellDesc: "Cell"
WorkDesc: "Work"
HomeDesc: "Home"
And then you pivot them together:
Name: Key field (repeats on all rows)
Description: CellDesc, WorkDesc, HomeDesc
PhoneNumber: CellNum, WorkNum, HomeNum
What you end up with post-pivot is:
Name, CellDesc, CellNum
Name, WorkDesc, WorkNum
Name, HomeDesc, HomeNum
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Which release of v8 do you have?
Do you have access to the loop functionality? If yes, just read each row of the file in as a single value and then use the field function to loop through the row outputting to the required fields.
eg loopvariable for description would be:
if @ITERATION = 1 then CELL else if @ITERATION = 2 then WORK else HOME.
Loopvariable for phone number would be:
InputRow.RawData[delimeter,@ITERATION,1] where delimiter is the delimiter in your file.
Only if you have Loop Variables though.
Do you have access to the loop functionality? If yes, just read each row of the file in as a single value and then use the field function to loop through the row outputting to the required fields.
eg loopvariable for description would be:
if @ITERATION = 1 then CELL else if @ITERATION = 2 then WORK else HOME.
Loopvariable for phone number would be:
InputRow.RawData[delimeter,@ITERATION,1] where delimiter is the delimiter in your file.
Only if you have Loop Variables though.
yes i am working on 8.7 version.,but i am not encounter with loop activity.
Can you please explain again.I tried with the following in the tranformer before pivot stage
loopvariable for description
if @ITERATION = 1 then DSLink2.Cell_Number else if @ITERATION = 2 then DSLink2.Work_Number else DSLink2.Home_Number
Loopvariable for phone number would be:
InputRow.RawData[',',@ITERATION,1] this is showing error .
File is comma delimter file.
Please explain me again how can i achieve result.
Do i need to add any new column or hardcode.
Can you please explain again.I tried with the following in the tranformer before pivot stage
loopvariable for description
if @ITERATION = 1 then DSLink2.Cell_Number else if @ITERATION = 2 then DSLink2.Work_Number else DSLink2.Home_Number
Loopvariable for phone number would be:
InputRow.RawData[',',@ITERATION,1] this is showing error .
File is comma delimter file.
Please explain me again how can i achieve result.
Do i need to add any new column or hardcode.
Last edited by hargun on Tue Mar 12, 2013 7:12 pm, edited 1 time in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You will need a premium membership to read the entirety of Craig's solution (for such it is).
Premium membership is not expensive, at less than 30c per day, and the revenue is devoted to keeping DSXchange alive by funding its hosting and bandwidth costs.
Premium membership is not expensive, at less than 30c per day, and the revenue is devoted to keeping DSXchange alive by funding its hosting and bandwidth costs.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Have you already split the input columns into separate columns is it still a single column containing the comma delimited record?
To do the looping:
1. Set up your 3 output columns: Name, Description, Phone
2. Set up 2 loop variables eg lvDescription, lvPhoneNum; Set the loop while to @ITERATION <=3
3. lvDescription = 'If @ITERATION = 1 then CELL else if @ITERATION = 2 then WORK else HOME'
4. lvPhoneNum: Depending on whether you have split your input into columns
4a. If split: 'if @ITERATION = 1 then InputRow.CEL#L else if @ITERATION = 2 then InputRow.WORK# else InputRow.HOME#'
4a. If not split ie still a single comma delimited value: 'InputRow.RawData[delimeter,@ITERATION,1]'
5. Asssign the loop variable to the correct output column
To do the looping:
1. Set up your 3 output columns: Name, Description, Phone
2. Set up 2 loop variables eg lvDescription, lvPhoneNum; Set the loop while to @ITERATION <=3
3. lvDescription = 'If @ITERATION = 1 then CELL else if @ITERATION = 2 then WORK else HOME'
4. lvPhoneNum: Depending on whether you have split your input into columns
4a. If split: 'if @ITERATION = 1 then InputRow.CEL#L else if @ITERATION = 2 then InputRow.WORK# else InputRow.HOME#'
4a. If not split ie still a single comma delimited value: 'InputRow.RawData[delimeter,@ITERATION,1]'
5. Asssign the loop variable to the correct output column
If your file format is really that simple , why dont you use a Transformer and funnel stage ? You dont have to worry about iterations and loop variables. (& is the name of your input link)
o/p link 1 : &Name , "CELL",&Cell.Number
o/p link 2 : &Name , "WORK",&Work.Number
o/p link 3 : &Name , "HOME",&Home.Number
Combine all 3 links in funnel( round robin) , you can use a sort funnel Keyed on Name.
Do you really have only ONE record ? I mean its a lot of work to develop a datastage job to process only one record
o/p link 1 : &Name , "CELL",&Cell.Number
o/p link 2 : &Name , "WORK",&Work.Number
o/p link 3 : &Name , "HOME",&Home.Number
Combine all 3 links in funnel( round robin) , you can use a sort funnel Keyed on Name.
Do you really have only ONE record ? I mean its a lot of work to develop a datastage job to process only one record