Page 1 of 1
How do to add a line for each value in the Column
Posted: Thu Jul 05, 2007 7:53 am
by DS_MJ
Hello:
How to add a line for each value in the Column?
Example:
Column value in COL_A is CH, SE, SR, etc,
So for COL_A , there will be 3 rows.
Posted: Thu Jul 05, 2007 8:09 am
by DSguru2B
Use the CONVERT() function and convert all commas with char(010) (new line character on unix).
This will be sufficient if there is only one column in your source. If you have multiple columns, then you can use the pivot stage.
Posted: Thu Jul 05, 2007 8:28 am
by DS_MJ
DSguru2B wrote:Use the CONVERT() function and convert all commas with char(010) (new line character on unix).
This will be sufficient if there is only one column in your source. If you have multiple columns, then you can use the pivot stage.
Thanks DSguru2B for the quick response. I will go ahead and try it.
Posted: Thu Jul 05, 2007 9:20 am
by DS_MJ
Hi DSguru2B
This is the first time I am using a Pivot Stage.
I have multiple columns on the source file. A Horizontal Pivot would create columns within a single row into many rows)
My Source file looks like this:
Code: Select all
COL_A | COL_B | COL_C | and so on
AC, AG, MA, MM, PH, RE, SR, VO | 1 | ZA0 | ......
MH, BO,NA | 2 | ZZZ |
Under COL_A I have values AC, AG, MA, MM, PH, RE, SR, VO and there should only be one value in this column, so there should be 8 rows here, one for each value and not just one.
SO My Target should populate Like below:
Code: Select all
COL_A | COL_B | COL_C | and so on
AC | 1 | ZA0 | ......
AG | 1 | ZA0 |
MA | 1 | ZA0 |
MM | 1 | ZA0 | ......
AG | 1 | ZA0 |
PH | 1 | ZA0 | ......
AG | 1 | ZA0 |
RE | 1 | ZA0 | ......
AG | 1 | ZA0 |
SR | 1 | ZA0 | ......
AG | 1 | ZA0 |
VO | 1 | ZA0 | ......
AG | 1 | ZA0 |
MH | 2 | ZZZ |
BO | 2 | ZZZ |
NA | 2 | ZZZ |
Can I achieve this with using Pivot stage...?
Posted: Thu Jul 05, 2007 9:24 am
by DSguru2B
Sure you can. Read about the pivot stage in its pdf help file. It explains with examples to give you a clear understanding.
Posted: Thu Jul 05, 2007 9:57 am
by DS_MJ
Hello DSguru2B:
I am using DS 7.5.2.
In the Help ---under Px Job Stages ---> Processing Stages I dont see the Pivot stage I see all the other stages, nor is it underRestructure stage. When I do a find it just gives the stage its Input and output pages and explanation about it. There is no example. Is there somewhere else I can get this..?
Posted: Thu Jul 05, 2007 10:03 am
by DSguru2B
GO to the Online documentation. Hit start on your desktop, go to Programs, find Ascential DataStage and go to Online Manuals.
Click on DataStage Documentation, then go to Plugin Stages. Once inside, search for Pivot Stage and read its manual.
You can also search here about pivot stage and real time examples that dsxians faced.
Posted: Thu Jul 05, 2007 11:43 am
by ray.wurlod
One of the examples in the manual (Pivot.pdf) is almost exactly your scenario.
Posted: Thu Jul 05, 2007 11:44 am
by DS_MJ
Hi DSguru2B:
Thanks I did find Pivot stage in the Plugins. The example showing in there has a difference. In the example it has different Columns that need pivoting. So it has columns Jan, Feb,Mar that are pivoted.
However, my job in 1 column (COL_A) it has data seperated by a comma and this seperated data from just COL_A needs to be pivoted.
I used the pivot stage and was able to pivot columns. But mine is
value in a column which is seperated by a comma that needs pivoting and am unable to do it.
So I was able to pivot some columns but i was unable to pivot the data in COL_A which is seperated by a comma.
Posted: Thu Jul 05, 2007 11:56 am
by ray.wurlod
Simply parse these data into separate columns. Use a Transformer stage with Field() functions or a Column Import stage.
Posted: Wed Jul 11, 2007 6:40 am
by Team_2007
Use EREPLACE fucntion to achieve this.
Posted: Wed Jul 11, 2007 10:11 am
by ray.wurlod
Yeah? How, precisely?