Columns into Rows

[sanred]

Hi,
I have Data coming in as

Ac_Key Order
------ -----
12345 P
12345 1
12345 2
12345 C

Now in next step it has to become one row like

12345 P12C

I want to concatenate 'Order' filed based on Ac_Key number.

Thanks in Advance

 

[blom0344]

This is the reverse process of what can be established using a normalizer.

It involves the following steps:

1. Create expression, create 4 outgoing ports each acting on the input of 'order' like:

IIF(Order = 'P','P',null)

2. Use an aggregator to denormalize the 4 records into one.
Aggregate over AC_key, using a min or max operator on the four ports.

3. Use a second expression transformation to concatenate the 4 ports into one outgoing string.


So, the strategy should be :

expression - aggregator - expression

If you need an example, drop me a mail..........

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

blom,

Is the no. of outgoing port based on the no. of rows for the AC_Key???? So if we can 6 rows then does that mean we need to have 6 ports.

Thanks
Sri

 

[sanred]

Thanks Bloom. Got solution in other way. Actually I did not put my question in correct way. I got solution using Transformation variables

 

[blom0344]

Yep,

It is directly proportional with the number of values order can take. So, with something like ordertype you will have a limited set. If you have no idea of the values that you can expect, this will not be a feasable strategy, cause you will not have any idea how many ports to define.

In the past I dabbled with these problems by using VBA instead of Informatica, but that was code I picked up somewhere (not being very able with VBA ..)



T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

sanred,

Possible to share the solution that you used. Is it dynamic or you need to know the exact now of rows for each unique value.

blom,

Many a thanks for your reply. Yeah not feasible if the no. of rows is not known predetermined. But is this VBA is in the Informatica or something external.

Thanks
Sri

 

[blom0344]

I use some VBA code in Access .....

T. Blom
Information analyst
tbl@shimano-eu.com