Lookup with WildCards
I have another transoformation question.
I have a look-up table similar to this
Product, Type NewProduct
-----------------------
ABC, XYZ NewProductA
ABC, YYZ NewProductB
AAA, Anything NewProductC (The anything in my real life sample has over 100 possibilities)
Is there a way to use a simple look-up transformation for all of these (my actual look-up table has about 200 possibilities) in the Product Column, or do I need to separate the 'Anythings' into there own look-up table? or maybe I will have to create a script
for this one?
Thoughts?
May 10th, 2011 5:06pm
A set of cascading lookups may work for you. But first, how do you know that "anything" should be looked up? Simply because you can't find a NewProduct for Product AAA Type XYZ? How is that different from the case where you don't find a
NewProduct for Product ABC Type WYZ?
A cascading lookup would consist of two lookups. First, you'd look for an exact match based on Product and Type. All of the lookup failures (the "no match" rows) would be send to another Lookup that would only use the Product as a lookup key.
You'd then Union All those rows together.
Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
May 10th, 2011 5:48pm
Thanks Todd,
Obvious when you think about it.
I can't get into specifics, suffice it to say this is the first of many goes at looking at the data from these legacy systems. Many passes will occur before GoLive.
May 10th, 2011 6:27pm