Column name in Pivot Stage
Moderators: chulett, rschirm, roy
Column name in Pivot Stage
Hello all,
I am trying to get "Column name" in derivation using Pivot stage. How can this be done?
Kommu
Eg:
SOURCE
Name, dept,sub1,sub2,sub3
joe,eng,5,5,7
Jim,dep,6,4,5
leo,gen,4,4,4
TARGET
Name,Dept,Sub,Marks
joe,eng,sub1,5
joe,eng,sub2,5
joe,eng,sub3,7
jim,dep,sub1,6
jim,dep,sub2,5
jim,dep,sub3,4
leo,gen,sub1,4
leo,gen,sub2,4
leo,gen,sub3,4
I am trying to get "Column name" in derivation using Pivot stage. How can this be done?
Kommu
Eg:
SOURCE
Name, dept,sub1,sub2,sub3
joe,eng,5,5,7
Jim,dep,6,4,5
leo,gen,4,4,4
TARGET
Name,Dept,Sub,Marks
joe,eng,sub1,5
joe,eng,sub2,5
joe,eng,sub3,7
jim,dep,sub1,6
jim,dep,sub2,5
jim,dep,sub3,4
leo,gen,sub1,4
leo,gen,sub2,4
leo,gen,sub3,4
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
Do you mean you need to get "sub1", "sub2" and "sub3" in the pivot stage.
For this you need to add a Column say "Sub" in the output link and then hard code these values in the Derivation column seperated by a comma (,).
Have a group by on Name and Dept.
I have never tried this before but I guess it should work.
Regards,
Sumit
For this you need to add a Column say "Sub" in the output link and then hard code these values in the Derivation column seperated by a comma (,).
Have a group by on Name and Dept.
I have never tried this before but I guess it should work.
Regards,
Sumit
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
You can create your 'Sub' column after the pivot using a stage variable in a transformer stage. Name the stage variable like SV1 and initialize it to 0. Then the logic would be something like 'If SV1 = 3 then 1 else SV1 + 1.' This will give you the 1,2,3 for each group and you can just cancatenate this to a constant 'Sub'.
how to get column name as a dat field with Pivot
Faced with a similar situation - how exactly do you take a column name and then access it inside the transform, to use the column name as data?
In my case I have coming in
one row with columns
LoanNum PRSRent ProRent PRSTax ProTax, etc - there are 12 sets of PRS/PRO columns
data is like this
LoanNum PRSRent ProRent PRSTax ProTax etc
1 $900.00 0 $19.00
1 0 $800.00 0 $18.00 ......
2 0 $500.00 0 $15.00 ......
etc
prs = present, pro = proposed (or future)
so there are 22 posibilities and I need a possible 22 rows from this one, and I need the column name (PRSRent, PRORent, etc) in the output row with the amount next to it:
LoanNum Item-Seq PresentExpType Amount ExpDescrip
1 1 y 900.00 PRSRent
1 2 y 19.00 PRSTax
1 3 n 800.00 Prorent
1 4 n 18.00 ProTax
so how would i get the column name PRSRent into the field ExpDescrip?
Is this done in the pivot?
thanks for any insights
RC99
In my case I have coming in
one row with columns
LoanNum PRSRent ProRent PRSTax ProTax, etc - there are 12 sets of PRS/PRO columns
data is like this
LoanNum PRSRent ProRent PRSTax ProTax etc
1 $900.00 0 $19.00
1 0 $800.00 0 $18.00 ......
2 0 $500.00 0 $15.00 ......
etc
prs = present, pro = proposed (or future)
so there are 22 posibilities and I need a possible 22 rows from this one, and I need the column name (PRSRent, PRORent, etc) in the output row with the amount next to it:
LoanNum Item-Seq PresentExpType Amount ExpDescrip
1 1 y 900.00 PRSRent
1 2 y 19.00 PRSTax
1 3 n 800.00 Prorent
1 4 n 18.00 ProTax
so how would i get the column name PRSRent into the field ExpDescrip?
Is this done in the pivot?
thanks for any insights
RC99
You would not be able to turn the column names into data in the pivot. You can do what you describe in a transformation stage after the pivot using stage variables. The first would be a counter and the second would be a big If statement to assign the description. The following is an example with just four columns but you can easily expand it to 22.
Code: Select all
svQuestionKey = If svQuestionKey = 4 Then 1 Else svQuestionKey + 1
svExpDescrip = If svQuestionKey = 1 then 'PRSRent'
Else If svQuestionKey = 2 Then 'ProRent'
Else If svQuestionKey = 3 Then 'PRSTax'
Else 'ProTax'
Keith
is there a way to simply access the column name , also - to
thanks
what I'm wondering - is there a way to simply access the column name - is it available as a data elemnet somehow in DS so you can simply access it, put the column name for the amount that goes with that column name into a column that would then be split out in the pivot.
I would want to do this before the Pivot, so you already have the name of the type of amount before you split out the amounts to separate rows.
I know it could be done by simply creating a column after the amt column and hardcoding the column name into it as var char
so in transform before the Pivot (or breakout)
derivation column name
ToXfmMap.PRSRENT > PRSRENT (this is the amount data)
'PRSRENT' > ExpDescription
etc for each
that is easier than doing an if/else
but we are trying to avoid any hardcoding and see what the extents of DS capacity is - to access different things - also if new columns are added - there would be less work than adding to a hardcoded solution. Also looking to avoid a lookup table - which of course could be updated easier - but adds more inputs, etc.
Thanks
what I'm wondering - is there a way to simply access the column name - is it available as a data elemnet somehow in DS so you can simply access it, put the column name for the amount that goes with that column name into a column that would then be split out in the pivot.
I would want to do this before the Pivot, so you already have the name of the type of amount before you split out the amounts to separate rows.
I know it could be done by simply creating a column after the amt column and hardcoding the column name into it as var char
so in transform before the Pivot (or breakout)
derivation column name
ToXfmMap.PRSRENT > PRSRENT (this is the amount data)
'PRSRENT' > ExpDescription
etc for each
that is easier than doing an if/else
but we are trying to avoid any hardcoding and see what the extents of DS capacity is - to access different things - also if new columns are added - there would be less work than adding to a hardcoded solution. Also looking to avoid a lookup table - which of course could be updated easier - but adds more inputs, etc.
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Take a look at the code generated by compiling a Transformer stage (in the RT_BPnnn directory on the server). You will see that the column names are not actually used; pseudo-names like Column%%3 are used instead.
This allows column names imported from actual tables, but which don't conform to the rules for variable names, to be handled satisfactorily (this is particularly important where the column names might be in Chinese, Japanese, Korean, etc., characters).
This allows column names imported from actual tables, but which don't conform to the rules for variable names, to be handled satisfactorily (this is particularly important where the column names might be in Chinese, Japanese, Korean, etc., characters).
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.
This post is 8 years old and from the 5.x version of DataStage, plus I'm not even sure what the "this" and "it" is that you are having an issue with. Always best to start your own post and provide the details of your issue and data examples, then we'll take it from there.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers