Checking for inequality using lookup file

[avenki77]

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.

[kris007]

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

Code: Select all

(SrcA = RefA) AND (SrcB <> RefB) AND (SrcC <> RefC) 

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?

[avenki77]

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.

[kris007]

Nice Catch I missed that. So, How about setting the derivation to the dummy column as INROWNUM and define it as a key?

[avenki77]

Even that is not possible, I guess. How would I know what rownum to look for in the hashfile?

[kris007]

Yeaaaahh Even that's not possible I guess. Had a long day.

[kris007]

What you can do is Join Ref1 to Source1 only and then in the constraint give the condition

Code: Select all

Ref2<>Src2 And Ref3<>Src3

That should work.

[meena]

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:

Code: Select all

(SrcA = RefA) AND (SrcB <> RefB) AND (SrcC <> RefC)

else you can write a stage variable:

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....

[avenki77]

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.

[chulett]

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.

[kris007]

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

Code: Select all

Src2<>Ref2 And Src3<>Ref3

. That should work. Else, you got give out a full detailed requirement.

[meena]

Hi avenki77,
I can suggest what Kris007 asked you to do and if "you tried it" then SQL else

[ray.wurlod]

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.

[sb_akarmarkar]

Try with ODBC lookup , if lookup contains less number of rows

Thnaks,
Anupam