How to write column headers to out put
Moderators: chulett, rschirm, roy
How to write column headers to out put
Hi all
i need to convert columns in to rows , for that i am using pivot stage
but my requirement is to write column headers for corrsepong values.
EXAMPLE;
i have 5 columns (INPUT)
ID, SAR-A, SAR-B, SAR-C, SAR-D
1 0.0 0.0 1.0 10.0
2 1.0 2.0 4.0 5.0
required output should look like this
ID SAR_ID VALUE
1 SAR-A 0.0
1 SAR-B 0.0
1 SAR-C 1.0
1 SAR-D 10.0
2 SAR-A 1.0
2 SAR-B 2.0
2 SAR-C 4.0
2 SAR-D 5.0
but when i use pivot stage
to convert cloumns to rows, i am getting like this
ID VALUE
1 0.0
1 0.0
1 1.0
1 10.0
2 1.0
2 2.0
2 4.0
2 5.0
any suggestions in getting required ouput
thanks in advance
i need to convert columns in to rows , for that i am using pivot stage
but my requirement is to write column headers for corrsepong values.
EXAMPLE;
i have 5 columns (INPUT)
ID, SAR-A, SAR-B, SAR-C, SAR-D
1 0.0 0.0 1.0 10.0
2 1.0 2.0 4.0 5.0
required output should look like this
ID SAR_ID VALUE
1 SAR-A 0.0
1 SAR-B 0.0
1 SAR-C 1.0
1 SAR-D 10.0
2 SAR-A 1.0
2 SAR-B 2.0
2 SAR-C 4.0
2 SAR-D 5.0
but when i use pivot stage
to convert cloumns to rows, i am getting like this
ID VALUE
1 0.0
1 0.0
1 1.0
1 10.0
2 1.0
2 2.0
2 4.0
2 5.0
any suggestions in getting required ouput
thanks in advance
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In a Pivot stage, there is no legal way. Unless you know how to extract column names from the metadata in the Repository, there is no available mechanism.Luk wrote:is it possible to get column names autoamaticaly (without writing their names manualy from keyboard)??
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.
hi ray ,
column names are loaded manually in other table.
can i merge both pivot output and table.
is it possible to get corresponding column name for its coreesponding value.
ray.wurlod wrote:In a Pivot stage, there is no legal way. Unless you know how to extract column names from the metadata in the Repository, there is no available mechanism.Luk wrote:is it possible to get column names autoamaticaly (without writing their names manualy from keyboard)??
column names are loaded manually in other table.
can i merge both pivot output and table.
is it possible to get corresponding column name for its coreesponding value.
You can do it with a simple trick.is it possible to get corresponding column name for its coreesponding value.
When you are passing your data to Pivot stage, concatenate SAR_ID and Value with some delemeter in between.
example:
Code: Select all
1 SAR-A|0.0
1 SAR-B|0.0
1 SAR-C|1.0
1 SAR-D|10.0
Code: Select all
Field(yourstring, "|", 1) and Field(yourstring, "|", 2)
That should do it.
Kris~
Hi kris, thanks for ur reply
i did not get you ,
what i am doing in pivot stage output is as follows
pivot/ output tab/column tab ;
i am writing coumn names manually
ID
VALUE SAR-A,SAR-B,SAR-C,SAR-D (in derivation field of VALUE)
i did not get you ,
what i am doing in pivot stage output is as follows
pivot/ output tab/column tab ;
i am writing coumn names manually
ID
VALUE SAR-A,SAR-B,SAR-C,SAR-D (in derivation field of VALUE)
thankskris wrote:You can do it with a simple trick.is it possible to get corresponding column name for its coreesponding value.
When you are passing your data to Pivot stage, concatenate SAR_ID and Value with some delemeter in between.
example:
Then you can parse both fields using Field function your way out of Pivot.Code: Select all
1 SAR-A|0.0 1 SAR-B|0.0 1 SAR-C|1.0 1 SAR-D|10.0
Have an output link from Pivot to Transformer and parse them your way out.Code: Select all
Field(yourstring, "|", 1) and Field(yourstring, "|", 2)
That should do it.
Kris~
Hope you understood the logic....wanted to finish what i have started.
The output of pivot will look like:
1, SAR-A|0.0
1, SAR-B|0.0
1, SAR-C|1.0
1, SAR-D|10.0
2, SAR-A|1.0
2, SAR-B|2.0
2, SAR-C|4.0
2, SAR-D|5.0
For Parsing in the last transformer, you will write these derivations:
Your final out put is now:
1, SAR-A, 0.0
1, SAR-B, 0.0
1, SAR-C, 1.0
1, SAR-D, 10.0
2, SAR-A, 1.0
2, SAR-B, 2.0
2, SAR-C, 4.0
2, SAR-D, 5.0
Good luck
Kris~
The output of pivot will look like:
1, SAR-A|0.0
1, SAR-B|0.0
1, SAR-C|1.0
1, SAR-D|10.0
2, SAR-A|1.0
2, SAR-B|2.0
2, SAR-C|4.0
2, SAR-D|5.0
For Parsing in the last transformer, you will write these derivations:
Code: Select all
COLUMN NAME DERIVATION
ID ---------> DSLink.F_ID
SAR_ID ---------> Field(DSLink.F_VALUE, '|', 1)
VALUE ---------> Field(DSLink.F_VALUE, '|', 2)
1, SAR-A, 0.0
1, SAR-B, 0.0
1, SAR-C, 1.0
1, SAR-D, 10.0
2, SAR-A, 1.0
2, SAR-B, 2.0
2, SAR-C, 4.0
2, SAR-D, 5.0
Good luck
![Cool 8)](./images/smilies/icon_cool.gif)
Kris~
Thank you very much Kris and Ray . i got it through 'Ray's suggetsion . By taking extra columns as the same number of column headers what i am pivoting . hard coding column headers into these extracolumns in transformer.
after that pivoting both using pivot stage.
after that pivoting both using pivot stage.
kris wrote:Hope you understood the logic....wanted to finish what i have started.
The output of pivot will look like:
1, SAR-A|0.0
1, SAR-B|0.0
1, SAR-C|1.0
1, SAR-D|10.0
2, SAR-A|1.0
2, SAR-B|2.0
2, SAR-C|4.0
2, SAR-D|5.0
For Parsing in the last transformer, you will write these derivations:Your final out put is now:Code: Select all
COLUMN NAME DERIVATION ID ---------> DSLink.F_ID SAR_ID ---------> Field(DSLink.F_VALUE, '|', 1) VALUE ---------> Field(DSLink.F_VALUE, '|', 2)
1, SAR-A, 0.0
1, SAR-B, 0.0
1, SAR-C, 1.0
1, SAR-D, 10.0
2, SAR-A, 1.0
2, SAR-B, 2.0
2, SAR-C, 4.0
2, SAR-D, 5.0
Good luck![]()
Kris~