Column name in Pivot Stage

[kommven]

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

[sumitgulati]

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

[kommven]

Hard coding is of no use. I have came up with a solution, I am appending the name of column before every data (as Varchar) and after pivoting, I am again doing a if then else condition and seperating char and digits and using appro...

If anyone knows better solution please send it to me...

[sumitgulati]

You can also try adding three column in the transformer stage prior to pivot stage and hard code their value to "sub1", "sub2" and "sub3". Then use these columns in the pivot stage.

This way you can get rid of the appending and seperating stuff.

Regards,
Sumit

[kommven]

Adding 3 columns before Pivot stage with constant value and deriving them in Pivot stage worked fine,,,

Thanks for your advice...

[KeithM]

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'.

[RC99]

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

[KeithM]

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'

[RC99]

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

[ray.wurlod]

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).

[MrBlack]

Could you explain this in greater detail. I've been trying to make it work but I can't ever get my column name values to appear after the pivot.

[chulett]

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.