Checking for inequality using lookup file
Moderators: chulett, rschirm, roy
Checking for inequality using lookup file
Hi,
My reference (lookup) file is having the following columns:
RefA
RefB
RefC
RefData1
My data (source) file is having the following columns:
SrcA
SrcB
SrcC
The lookup i want to perform is like this:
If (SrcA = RefA) AND (SrcB <> RefB) AND (SrcC <> RefC)
Then I need to pick up RefData1
If I build a hashfile with RefA, RefB and RefC as key fields, then how can I check (SrcB <> RefB) AND (SrcC <> RefC)? Or is there any other way to achieve this?
Thanks in advance.
My reference (lookup) file is having the following columns:
RefA
RefB
RefC
RefData1
My data (source) file is having the following columns:
SrcA
SrcB
SrcC
The lookup i want to perform is like this:
If (SrcA = RefA) AND (SrcB <> RefB) AND (SrcC <> RefC)
Then I need to pick up RefData1
If I build a hashfile with RefA, RefB and RefC as key fields, then how can I check (SrcB <> RefB) AND (SrcC <> RefC)? Or is there any other way to achieve this?
Thanks in advance.
In your reference hashed file create a dummy column say of datatype char and lenght 1 and define it as the Key column. Now, within the Transformer stage in the Key Expression hardcode it as 'Z'. Now, in the Transformer define a constraint with your condition
That should get you what you want.
Also, what happens when you join all your keys from your source and reference and define the same condition in the constraint?
Code: Select all
(SrcA = RefA) AND (SrcB <> RefB) AND (SrcC <> RefC)
Also, what happens when you join all your keys from your source and reference and define the same condition in the constraint?
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Kris,
Thanks for the response.
As you say, if I create a hashfile with a dummy key column,
then what will populate that column inside the hashfile with. Say if I populate a constant value (eg. "*") then, I guess I can have only one row in the hashfile. Am I right?
I have various different combinations of A, B and C in my reference data.
For example:
RefA1, RefB1, RefC1
RefA1, RefB1, RefC2
RefA1, RefB2, RefC1
RefA1, RefB2, RefC2
RefA1, RefB3, RefC1
RefA1, RefB3, RefC2
Pls advise.
Thanks for the response.
As you say, if I create a hashfile with a dummy key column,
then what will populate that column inside the hashfile with. Say if I populate a constant value (eg. "*") then, I guess I can have only one row in the hashfile. Am I right?
I have various different combinations of A, B and C in my reference data.
For example:
RefA1, RefB1, RefC1
RefA1, RefB1, RefC2
RefA1, RefB2, RefC1
RefA1, RefB2, RefC2
RefA1, RefB3, RefC1
RefA1, RefB3, RefC2
Pls advise.
What you can do is Join Ref1 to Source1 only and then in the constraint give the conditionThat should work.
Code: Select all
Ref2<>Src2 And Ref3<>Src3
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Hi..
You can try doing in this way:
HASH FILE:RefA(KEY),RefB RefC RefData1.
Give this as a look up based on the key and write a constraint in the transformer:
else you can write a stage variable:
and set a constraint of stage variable=@true
And if want to pass the 4 th column then you can pass it directly....
You can try doing in this way:
HASH FILE:RefA(KEY),RefB RefC RefData1.
Give this as a look up based on the key and write a constraint in the transformer:
Code: Select all
(SrcA = RefA) AND (SrcB <> RefB) AND (SrcC <> RefC)
Code: Select all
If (SrcA = RefA) AND (SrcB <> RefB) AND (SrcC <> RefC) then @true else @flase
and set a constraint of stage variable=@true
And if want to pass the 4 th column then you can pass it directly....
Hi Meena,
Thanks! But I already tried this and I faced a problem:
If I take the same set of data I mentioned before,
RefA1, RefB1, RefC1
RefA1, RefB1, RefC2
RefA1, RefB2, RefC1
RefA1, RefB2, RefC2
RefA1, RefB3, RefC1
RefA1, RefB3, RefC2
If I write this into the hashfile, I will finally have only one row in the hashfile i.e, RefA1, RefB3, RefC2.
Thanks! But I already tried this and I faced a problem:
If I take the same set of data I mentioned before,
RefA1, RefB1, RefC1
RefA1, RefB1, RefC2
RefA1, RefB2, RefC1
RefA1, RefB2, RefC2
RefA1, RefB3, RefC1
RefA1, RefB3, RefC2
If I write this into the hashfile, I will finally have only one row in the hashfile i.e, RefA1, RefB3, RefC2.
Of course, that's how hashed files work! Destructive overwrite based on the defined keys - Last One In Wins.
For all the reasons you've already run through, a simple hashed file isn't going to work for you here. Use them for an equality check.
That being said, I don't really understand what you need to do here. For every value of RefA1 you've got several rows that look like they would qualify for your 'inequality' check... or at least you could. If there are several, do you want them all? Seems to me that answer would help drive the solution. Unless I'm really missing something here.
Regardless, it seems to me that you'll need a SQL solution. That means directly via a DB lookup or perhaps something using a UV stage over a UniVerse table or a carefully crafted hashed file. A native database lookup would get you a single row but you could control which row it was if you wanted. An ODBC stage, like the UV stage, would allow you to return a 'multi-row result set' if needed.
So help us understand exactly how you need it to work.
For all the reasons you've already run through, a simple hashed file isn't going to work for you here. Use them for an equality check.
That being said, I don't really understand what you need to do here. For every value of RefA1 you've got several rows that look like they would qualify for your 'inequality' check... or at least you could. If there are several, do you want them all? Seems to me that answer would help drive the solution. Unless I'm really missing something here.
Regardless, it seems to me that you'll need a SQL solution. That means directly via a DB lookup or perhaps something using a UV stage over a UniVerse table or a carefully crafted hashed file. A native database lookup would get you a single row but you could control which row it was if you wanted. An ODBC stage, like the UV stage, would allow you to return a 'multi-row result set' if needed.
So help us understand exactly how you need it to work.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I was about to ask few of those questions what Craig has asked. Ok. Based upon what you have given heres what I would do. Do a full outer Join using the Merge stage based upon Ref1 and Src1 as keys and bring in all the columns from both the files. Now pass them through a Transformer and then in the constraint give the condition as . That should work. Else, you got give out a full detailed requirement.
Code: Select all
Src2<>Ref2 And Src3<>Ref3
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This might be more easily solved with three hashed files, each with a single key column, and appropriate constraint expressions testing the results of the individual lookups.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact: