Non-equijoin Key Expression

[jjrbikes]

Good Afternoon -

I'm definitely not firing on all cylinders today and need a little clarification. I've searched the forum - but can't find anything that directly addresses my current issue- so here it is:

I have a hashed file with Customer_Number as my key field.
I also have an input file with multiple columns - including My_Customer_Number, Your_Customer_Number, and Our_Customer_Number.

If I set my 'key_expression' as:

Code: Select all

My_Customer_Number OR Your_Customer_Number OR Our_Customer_Number

Will

Code: Select all

NOT(LookUpFile.NOTFOUND)

evaluate to True if My_Customer_Number = Customer_Number OR Your_Customer_Number = Customer_Number OR Our_Customer_Number = Customer_Number.

Thanks!

[DSguru2B]

No. It wont.

[jjrbikes]

Talk about effective and efficient...thanks for that Guru.

So, can you help me out a bit here and tell me what I need to do to get the intended result? My hope is to use

Code: Select all

NOT(LookUpFile.NOTFOUND) And (X <> Y or P <> Q or A <> C)

as my constraint for one output file and

Code: Select all

LookUpFile.NOTFOUND

as my constraint for a second output file.

Thanks again.

[paddu]

jjrbikes- truly did not understand your second post.

[jjrbikes]

sorry -
My second post is asking for help. If the code I described in my first post will not create a True evaluation for

Code: Select all

NOT(LookUpFile.NOTFOUND)

- What do I need to do to my code to get the intended result?

In other words, how must I modify my 'key expression' in order to get

Code: Select all

NOT(LookUpFile.NOTFOUND)

to evaluate to TRUE when any one of three possible input columns match the key column on my hashed file?

Does that help?

Thanks

[DSguru2B]

I have no idea what X, Y, P, Q, A and C are. Reading your second post, you can get that done with a custom routine. Something like

Code: Select all

      Retvalue=""

      Open "suresh" to myfile else ABORT

      Readu Retvalue from myfile, Arg1 else
         Readu Retvalue from myfile, Arg2 else
            Readu Retvalue from myfile, Arg3 else
               Retvalue = -1
            End
         End
      End

      Ans = Retvalue

    

Its very raw right now. You need to add error handling. This is just to give an idea.

[I_Server_Whale]

You can have three reference links coming into the transformer from same the hashed file look-up.
One each for performing the lookup based on the three incoming fields.

HTH,
Whale.

[jjrbikes]

thanks -

X, Y, P, Q, A and C were just examples that there was more code to my second output file and I could therefore not simply use the "Reject Row" option. Really insignificant here.

On to the more important stuff.

If I'm reading your code correctly, this would search my hashed file for the three possible values in the input file, and if not found would return basically a LookUpFile.NOTFOUND value. Correct?

Thanks - I'll give this a try.

[jjrbikes]

Thanks Whale -
that is actually how we currently have our process set up - but for some reason someone doesn't like that design and tasked me with making this work with only one link coming into the transformer.

I like the way you think!

[DSguru2B]

If I'm reading your code correctly, this would search my hashed file for the three possible values in the input file, and if not found would return basically a LookUpFile.NOTFOUND value. Correct?

It will return a -1 if the value is not found. You can leverage that for LookUpFile.NOTFOUND

[paddu]

jjrbikes

are you carrying these three source fields to target or are you carrying only the reference lookup (field ) to the target ? or not carrying any of these fields to the target only using them for selection of the data??

paddu

[I_Server_Whale]

Thanks!

that is actually how we currently have our process set up - but for some reason someone doesn't like that design

But Why? May I know the reason?
Whale.

[chulett]

If I set my 'key_expression' as:

Code: Select all

My_Customer_Number OR Your_Customer_Number OR Our_Customer_Number

Not even close to a valid Key Expression. An expression like this will resolve to one of two values: TRUE or FALSE. A zero or a one.

You can conditionally supply one of the three values to the field via something like this:

Code: Select all

If X Then My_Customer_Number Else If Y Then Your_Customer_Number Else Our_Customer_Number

If you want to look up all three you must use three reference links. Then it's a matter of checking the results in whatever dependancy order you may have.

[jjrbikes]

Good Morning -

Whale -
The only explanation provided was that it was "messy, hard to follow and even more difficult to debug and maintain". But, now I know for sure that this is truly the best, if not the only, way to write this piece of code.

Thanks to Everyone for your help on this!

[bkerebel]

hi,
if I have trouble about expressions like that, I do one variable for each TRUE expression, and then it's very easy to debug,

vars :
true_1 : if NOT(LookUpFile.NOTFOUND) then @true else @false
true_2 : X <> Y or P <> Q or A <> C then @true else @false
derivation :
true_1 AND true_2

and then your not_easy_someone will be happy to see that your work is so easy to debug

Bertrand