Lookup Failure Default Value
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 145
- Joined: Thu Oct 26, 2017 10:43 am
Lookup Failure Default Value
Hey everyone,
I wanted to ask a simple question, does anyone know where I would set the default value for a failed lookup that is set to continue? In a numeric lookup the default we get is a 0, we're hoping we can just change the default returned value as opposed to adding additional logic to look for 0s and change them. I'm open to any suggestions. Thanks in advance!!
I wanted to ask a simple question, does anyone know where I would set the default value for a failed lookup that is set to continue? In a numeric lookup the default we get is a 0, we're hoping we can just change the default returned value as opposed to adding additional logic to look for 0s and change them. I'm open to any suggestions. Thanks in advance!!
Last edited by jackson.eyton on Wed Jan 24, 2018 10:43 am, edited 1 time in total.
-Me
-
- Premium Member
- Posts: 145
- Joined: Thu Oct 26, 2017 10:43 am
I am not sure that you can change the default value. One option is that you could make the column coming from the reference link as nullable. In a subsequent transformer stage you can build some logic to replace the nulls with the default value of your choice. Alternatively, you can send the lookup failures to a reject link, and add specific values for the column in question and then funnel back to the main stream.
It all depends upon what is the overall goal.
Hope this helps,
Raj
It all depends upon what is the overall goal.
Hope this helps,
Raj
-
- Premium Member
- Posts: 145
- Joined: Thu Oct 26, 2017 10:43 am
dsguy1247,
I kind of figured this was the route we'd have to go. I could have sworn setting a default lookup failure value was an option somewhere from the courses I took, but I wonder thats for string lookups only? In either case I believe you're correct and we'll be opting to send failed lookups to rejects and we'll set the value from a subsequent transformer from there.
I kind of figured this was the route we'd have to go. I could have sworn setting a default lookup failure value was an option somewhere from the courses I took, but I wonder thats for string lookups only? In either case I believe you're correct and we'll be opting to send failed lookups to rejects and we'll set the value from a subsequent transformer from there.
-Me
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 145
- Joined: Thu Oct 26, 2017 10:43 am
Hmm that might be it, here is a pretty interesting article about that stage.
https://it.toolbox.com/blogs/vincentmcb ... ter-110807
It sounds like I could use this stage to handle those lookup values though. Thanks guys!
https://it.toolbox.com/blogs/vincentmcb ... ter-110807
It sounds like I could use this stage to handle those lookup values though. Thanks guys!
-Me
Jackson,
When I used to teach Datastage for IBM, I used to teach that modify stage is much less expensive than the transformer stage, hence when performance is critical, it is better to use modify stage. Then, during one of the debugging sessions with IBM tech support for a client, the support guy said that the in the latest versions, the CPU utilization was no longer a huge differentiator. I did not question him.
However, the drag and drop features of most of the stages, where you can drag the input columns to output, is not available in the modify stage, which makes it a little painful and prone to silly typo errors.
On the original post, I believe you have a work around, but I just wanted to share my two cents on my "favorite" modify stage BTW, the Vincent article was great and it makes a lot of sense. In fact, any article that quotes Ray Wurlod is just awesome.
Thanks,
Raj
When I used to teach Datastage for IBM, I used to teach that modify stage is much less expensive than the transformer stage, hence when performance is critical, it is better to use modify stage. Then, during one of the debugging sessions with IBM tech support for a client, the support guy said that the in the latest versions, the CPU utilization was no longer a huge differentiator. I did not question him.
However, the drag and drop features of most of the stages, where you can drag the input columns to output, is not available in the modify stage, which makes it a little painful and prone to silly typo errors.
On the original post, I believe you have a work around, but I just wanted to share my two cents on my "favorite" modify stage BTW, the Vincent article was great and it makes a lot of sense. In fact, any article that quotes Ray Wurlod is just awesome.
Thanks,
Raj
Ray has made the same observation in the past as well. I also did not question him.dsguy1247 wrote:the support guy said that the in the latest versions, the CPU utilization was no longer a huge differentiator. I did not question him.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers