Vertical Pivot Stage
Moderators: chulett, rschirm, roy
Vertical Pivot Stage
Hi,
I have the requirement to convert rows to columns.So,i used vertical pivot stage.But it works when the number of records for each key are same.But,my input is as follows.
col1,col2
1,C1
1,C2
1,C3
2,C1
2,C3
So,output should be
1,C1,C2,C3
2,C1,Null,C3
Please suggest me how can i do this in vertical pivot stage
I have the requirement to convert rows to columns.So,i used vertical pivot stage.But it works when the number of records for each key are same.But,my input is as follows.
col1,col2
1,C1
1,C2
1,C3
2,C1
2,C3
So,output should be
1,C1,C2,C3
2,C1,Null,C3
Please suggest me how can i do this in vertical pivot stage
Hi,
I came across the post saying viewtopic.php?t=145198 that it can be done in a transformer using loop variables.Could anyone please give an idea how can i achieve this? and get the following output.
ID,Item1,Item2,Item3,Item4,Item5,Item6,Item7,Item8
001,a1,Null,null,null,Null,Null,Null,null
001,a1,Null,a2,null,Null,Null,Null,null
001,a1,Null,a2,a3,Null,Null,Null,null
001,a1,Null,a2,a3,Null,Null,Null,a4
I came across the post saying viewtopic.php?t=145198 that it can be done in a transformer using loop variables.Could anyone please give an idea how can i achieve this? and get the following output.
ID,Item1,Item2,Item3,Item4,Item5,Item6,Item7,Item8
001,a1,Null,null,null,Null,Null,Null,null
001,a1,Null,a2,null,Null,Null,Null,null
001,a1,Null,a2,a3,Null,Null,Null,null
001,a1,Null,a2,a3,Null,Null,Null,a4
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Deepa, welcome back again
We have discussed almost something similar in the below link.
viewtopic.php?t=144616&highlight=
I have never heard of Vertical Pivot stage in any DataStage version. There is a pivot stage which can do both horizontal and vertical pivots from version 8.5. Earlier versions can do horizontal pivots only, though vertical can be achieved in transformer stage.
We have discussed almost something similar in the below link.
viewtopic.php?t=144616&highlight=
I have never heard of Vertical Pivot stage in any DataStage version. There is a pivot stage which can do both horizontal and vertical pivots from version 8.5. Earlier versions can do horizontal pivots only, though vertical can be achieved in transformer stage.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
Hi Kandy,kandyshandy wrote:I have never heard of Vertical Pivot stage in any DataStage version. .
I mean doing vertical pivot in pivot stage only.
Actually the earlier post was to pivot the source data irrespective of number of records.
now,i need to consider maximum number of records (in sample data,it have shown 3.actually it is 45 in my requirement)and insert null if a record is not present while pivoting.
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Just thinking out loud as don't know how well it will work...
You could generate x number of rows to match all your possible element_name field values (i.e. id, name, place etc), do an outer join to your source so that each F_N joins to every generated row, this will then ensure every F_N has every possible element. From there do your vertical pivot.
The only hiccup springing to mind is binding the F_N to the generated rows...
You could generate x number of rows to match all your possible element_name field values (i.e. id, name, place etc), do an outer join to your source so that each F_N joins to every generated row, this will then ensure every F_N has every possible element. From there do your vertical pivot.
The only hiccup springing to mind is binding the F_N to the generated rows...
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Sort and partition by F_N.
In stage variables:
If you have an earlier version than 8.5 follow the Transformer stage with a Remove Duplicates stage, group on F_N and preserve Last.
In stage variables:
- Map ID to svID if element_name is "ID" otherwise leave svID untouched.Map Name to svName if element_name is "Name" otherwise leave svName untouched.
Code: Select all
svID <== If InLink.element_name = "ID" Then InLink.ID Else svID
Map Value to svValue if element_name is "Value" otherwise leave svValue untouched.
If you have an earlier version than 8.5 follow the Transformer stage with a Remove Duplicates stage, group on F_N and preserve Last.
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.
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Deepa... Here is something you can try but one additional requirement is highlighted in bold
This is purely from thought process and never been tested.
Sample Input:
1,ID,13
1,Name,xx
1,Place,yy
S,SSSS,SS --> (Add this dummy row to your input file as last row)
Requisites:
Ensure that the records with same F_N is in one partition & ordered.
Stage Variables:
CurrID = Col1
WriteCheck = [If CurrID <> PrevID Then 'WRITE' Else 'DROP']
svPrevCol1 = svCol1
svPrevCol2 = svCol2
svPrevCol3 = svCol3
svPrevCol4 = scCol4
PrevID = CurrID
svCol1 = F_N
svCol2 = [If Col2 = 'ID' Then Col3 Else svCol2]
svCol3 = [If Col2 = 'Name' Then Col3 Else svCol3]
svCol4 = [If Col2 = 'Place' Then Col3 Else svCol4]
Constraint:
@INROWNUM <> 1 and WriteCheck = 'WRITE'
Mapping:
svPrevCol1 -> Col1
svPrevCol2 -> Col2
svPrevCol3 -> Col3
svPrevCol4 -> Col4
All the best
This is purely from thought process and never been tested.
Sample Input:
1,ID,13
1,Name,xx
1,Place,yy
S,SSSS,SS --> (Add this dummy row to your input file as last row)
Requisites:
Ensure that the records with same F_N is in one partition & ordered.
Stage Variables:
CurrID = Col1
WriteCheck = [If CurrID <> PrevID Then 'WRITE' Else 'DROP']
svPrevCol1 = svCol1
svPrevCol2 = svCol2
svPrevCol3 = svCol3
svPrevCol4 = scCol4
PrevID = CurrID
svCol1 = F_N
svCol2 = [If Col2 = 'ID' Then Col3 Else svCol2]
svCol3 = [If Col2 = 'Name' Then Col3 Else svCol3]
svCol4 = [If Col2 = 'Place' Then Col3 Else svCol4]
Constraint:
@INROWNUM <> 1 and WriteCheck = 'WRITE'
Mapping:
svPrevCol1 -> Col1
svPrevCol2 -> Col2
svPrevCol3 -> Col3
svPrevCol4 -> Col4
All the best
Last edited by kandyshandy on Mon Apr 09, 2012 4:13 am, edited 1 time in total.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!
Recent versions of DataStage add a mechanism (system variable?) to detect the last record in your input along with some other useful ones. Off the top of my head I don't recall their exact names. This to avoid having to add any kind of 'dummy' row.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Hi,
This is resolved.I tried the following.
records with same F_N is taken in one partition by selecting appropriate partioning method.
Stage Variables:
next = F_N
ic2=if (next<>prev) then '' else c2
ic1=if (next<>prev) then '' else c1
c1=if ELEMENT_NAME='ID' then VALUE else ic1
c2=if ELEMENT_NAME=Name' then VALUE else ic2
prev=next
Constraint:
LastRowInGroup(F_N)
output columns:
ID=c1
Name=c2
File_Name=F_N
This is resolved.I tried the following.
records with same F_N is taken in one partition by selecting appropriate partioning method.
Stage Variables:
next = F_N
ic2=if (next<>prev) then '' else c2
ic1=if (next<>prev) then '' else c1
c1=if ELEMENT_NAME='ID' then VALUE else ic1
c2=if ELEMENT_NAME=Name' then VALUE else ic2
prev=next
Constraint:
LastRowInGroup(F_N)
output columns:
ID=c1
Name=c2
File_Name=F_N