Page 1 of 1
How to write column headers to out put
Posted: Thu Jun 02, 2005 4:42 pm
by harithay
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
Posted: Thu Jun 02, 2005 7:27 pm
by ray.wurlod
Add an extra non-key column on which to pivot. It contains a constant, being a comma-separated list of your (literal) column headings.
Posted: Fri Jun 03, 2005 1:18 am
by Luk
is it possible to get column names autoamaticaly (without writing their names manualy from keyboard)??
Posted: Fri Jun 03, 2005 4:31 am
by Sainath.Srinivasan
You can import from the source.
Posted: Fri Jun 03, 2005 4:35 am
by Luk
You can import from the source.
but I want them as rows and from source I can import them as rows only from system tables !!
I can also import column names as meta data-but whhat I asked is if I could use some tip in transformer to get column names in this stage??
Posted: Fri Jun 03, 2005 6:06 am
by ray.wurlod
Luk wrote:is it possible to get column names autoamaticaly (without writing their names manualy from keyboard)??
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.
Posted: Fri Jun 03, 2005 6:22 am
by Luk
how to extract column names from the metadata in the Repository,
exactly that was the core of my question - I presume that in DS I can not do this!
Posted: Fri Jun 03, 2005 9:04 am
by harithay
hi ray ,
ray.wurlod wrote:Luk wrote:is it possible to get column names autoamaticaly (without writing their names manualy from keyboard)??
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.
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.
Posted: Fri Jun 03, 2005 9:28 am
by kris
is it possible to get corresponding column name for its coreesponding value.
You can do it with a simple trick.
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
Then you can parse both fields using Field function your way out of Pivot.
Code: Select all
Field(yourstring, "|", 1) and Field(yourstring, "|", 2)
Have an output link from Pivot to Transformer and parse them your way out.
That should do it.
Kris~
Posted: Fri Jun 03, 2005 10:54 am
by harithay
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)
kris wrote:
is it possible to get corresponding column name for its coreesponding value.
You can do it with a simple trick.
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
Then you can parse both fields using Field function your way out of Pivot.
Code: Select all
Field(yourstring, "|", 1) and Field(yourstring, "|", 2)
Have an output link from Pivot to Transformer and parse them your way out.
That should do it.
Kris~
thanks
Posted: Fri Jun 03, 2005 1:26 pm
by 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:
Code: Select all
COLUMN NAME DERIVATION
ID ---------> DSLink.F_ID
SAR_ID ---------> Field(DSLink.F_VALUE, '|', 1)
VALUE ---------> Field(DSLink.F_VALUE, '|', 2)
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~
Posted: Fri Jun 03, 2005 3:41 pm
by harithay
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.
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:
Code: Select all
COLUMN NAME DERIVATION
ID ---------> DSLink.F_ID
SAR_ID ---------> Field(DSLink.F_VALUE, '|', 1)
VALUE ---------> Field(DSLink.F_VALUE, '|', 2)
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~
Posted: Fri Jun 04, 2010 9:03 am
by hcdiss
Thank you Kris..it is helpful information.