How do to add a line for each value in the Column

DS_MJ · Post by **DS_MJ** » Thu Jul 05, 2007 7:53 am

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.

DSguru2B · Post by **DSguru2B** » Thu Jul 05, 2007 8:09 am

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.

DS_MJ · Post by **DS_MJ** » Thu Jul 05, 2007 8:28 am

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.

DS_MJ · Post by **DS_MJ** » Thu Jul 05, 2007 9:20 am

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

DSguru2B · Post by **DSguru2B** » Thu Jul 05, 2007 9:24 am

Sure you can. Read about the pivot stage in its pdf help file. It explains with examples to give you a clear understanding.

DS_MJ · Post by **DS_MJ** » Thu Jul 05, 2007 9:57 am

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

DSguru2B · Post by **DSguru2B** » Thu Jul 05, 2007 10:03 am

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.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 05, 2007 11:43 am

One of the examples in the manual (Pivot.pdf) is almost exactly your scenario.

DS_MJ · Post by **DS_MJ** » Thu Jul 05, 2007 11:44 am

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.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 05, 2007 11:56 am

Simply parse these data into separate columns. Use a Transformer stage with Field() functions or a Column Import stage.

Team_2007 · Post by **Team_2007** » Wed Jul 11, 2007 6:40 am

Use EREPLACE fucntion to achieve this.

ray.wurlod · Post by **ray.wurlod** » Wed Jul 11, 2007 10:11 am

Yeah? How, precisely?