logic required for horizontal pivot

hargun · Post by **hargun** » Sat Mar 09, 2013 2:39 pm

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.

chulett · Post by **chulett** » Sat Mar 09, 2013 5:16 pm

That's a horizontal pivot. Only thing 'tricky' about it is you have to add three new columns with your hardcoded values ('cell', etc) so you can pivot the numbers in pairs.

hargun · Post by **hargun** » Sat Mar 09, 2013 8:39 pm

Thanks Crig for response,

i tried using horizontal pivot and in derivation column given cell,home and work number but i am not getting the expected output.

Can you please tell me where i have to add new columns with hardcoded values.

chulett · Post by **chulett** » Sat Mar 09, 2013 9:36 pm

Transformer.

hargun · Post by **hargun** » Sun Mar 10, 2013 7:04 pm

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.

chulett · Post by **chulett** » Sun Mar 10, 2013 10:37 pm

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

ShaneMuir · Post by **ShaneMuir** » Mon Mar 11, 2013 9:20 am

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.

hargun · Post by **hargun** » Tue Mar 12, 2013 7:06 pm

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.

chulett · Post by **chulett** » Tue Mar 12, 2013 7:10 pm

FYI - you loop or pivot, not both... just in case you were trying both, hard to tell from your post.

hargun · Post by **hargun** » Tue Mar 12, 2013 7:14 pm

chullet,

i tried with suggestion provided but pivot not giving me the exact so i tried with alternate solution nut no luck.

i am more comfortable with pivot.

chullet,
can you give example as you told to hardcode before the pivot .Would you please elaborate this.

chulett · Post by **chulett** » Tue Mar 12, 2013 7:22 pm

Sorry but I already did.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 12, 2013 9:01 pm

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.

ShaneMuir · Post by **ShaneMuir** » Wed Mar 13, 2013 2:38 am

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

rameshrr3 · Post by **rameshrr3** » Thu Mar 14, 2013 10:41 am

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