How to write column headers to out put

[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

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

[Luk]

is it possible to get column names autoamaticaly (without writing their names manualy from keyboard)??

[Sainath.Srinivasan]

You can import from the source.

[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??

[ray.wurlod]

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.

[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!

[harithay]

hi ray ,

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.

[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~

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

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

[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~

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

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~

[hcdiss]

Thank you Kris..it is helpful information.