Normalizer Dynamic Occurs

[RubiconCSL]

Lets say you have an input file with a field of comma delimited phone numbers - you don't know how many there will be per record.

In the target database, you want one record per phone number.

In 'C' for example, you could do a while loop and insert one record per phone number as you traverse the string until you determine it has finished. A simple while loop or for loop for that matter.

How does Informatica do it? I don't think it can - easily anyway.

A normalizer requires a set number of 'occurs', but there could be any number?

I can't be the first person to need to do this - can I????

Any funky ideas.........

Dominic

 

[blom0344]

Hello Rubicon,

The input you have in mind is way beyond the source definition Powermart can handle. I think it would be a safe bet to throw this one to the VBA experts in one of the appropriate forum. Transforming the string into a single column file, you then feed it as a flatfile with a primary key in the targetfield. A simple insert will then ensure that you get only unique values in the targettable.... T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[VJ123]

Rubicon,

I think there is a way to do this in Informatica.

I assume there is a limit that how many phone numbers can appear in one row (may be 20 or 30?).
I take it as 20 for example.

Eg.for a source data like xyz,123-456-7890,894-345-9376,343-234-3215
define the source as flat file with id char(3), phone_no char(2000?) - (instead of defining 3 phone no columns).

In the designer use expression transformation to break the single column phone number into 20 pieces.
Of course, you will have 20 output ports. Next place a normalizer.
In the Normalizer tab, create 2 columns, ID with occurs =1 and phone_no with occurs=20.
Now link all Expr output ports to Norm input ports.
Place a filter next to Norm and reject records having null phone numbers.
Now you are ready to insert into target.

Please let me know how it helps you.

VJ

 

[RubiconCSL]

Hi VJ,

That is exactly what I have to do, but it isn't a dynamic occurs, in that you have to allow for upto x phone numbers.

It would be far more efficient if one could loop until you reach the end of the relevent data i.e. start at the beginning of the string and loop until you know there is no more. That way you don't have to do 20 bits of processing per row, where most rows may have just one phone number in.

Thanks for the input though. I guess it's just one more of those annoying parts of Informatica. Still, as a contractor it does do the most important thing well - get me a good daily rate!

Cheers,

Dominic