Join failing on Left Side

[rob_lawson]

I'm joining two inputs - one starts as a .yxdb and the other is a SQL pull against a DB2 Database. There are values pulled in where somevalues have only Integers - e.g. 2720 DED_LBL_CD and others have alphanumeric values - e.g. 2720R

I've formatted them as String, V-String, V-W String, etc and when I join on the two values some are still falling out on the Left Join even though both values are present on Left and Right, immediately before the Join tool. What do I need to do to get them lined up and fall into the J output where they should be?

[JohnJPS]

If something is only present in L, it goes to the L output, same for something only present in R going to the R output. To combine them all, just follow the Join by a Union and send all 3 Join outputs into the union.  :-)

[rob_lawson]

Thanks @JohnJPS 

The problem is that the values, that I'm joining on, ARE present in both right and left inputs but the Join tool is not recognizing that it is present on both sides.

Example:

There is a Deduction Label Code of 2625R (in the Left feed it's listed as Deduction Code - in the Right feed it's listed as DED CODE). The right side is pulling from SQL against our database, and the left side is an ASCII flat file.I've run through Data Cleansing tool and removed leading and trailing spaces, and I can physically see the right side data before the Join tool.

[danilang]

Hi @rob_lawson 

This sounds like an interesting case.  Can you dump a small sample of your data just before the left and right inputs of the join and post that here.  Try to include some values that match and some that don't.

Thanks

Dan

[rob_lawson]

Thanks @danilang 

Attaching a few things:

GMI WF Right Input Data - this is the data immediately before the Dynamic Input. The field in question is the Concat_QUERY DED LABEL. That's what is replacing a SQL string in the Dynamic Input
GMI WF after Dynamic Join Data - this is the data that is coming out after the SQL replace. The QUERY DED LABEL should be pulled into the DED CODE column. As you can see, there are values in the first tool that are not being pulled in here.

The third item is a screen shot of the SQL Replace String command.

Finally, here's a screen shbot of the actual SQL I'm running against our Oracle DB.

 

I've tried changing the Dynamic Input, which is clearly stripping off some values, to a SQL: Update WHERE Clause and cannot get anything in the Conact_DED LABEL LIST to pass through.

Thanks for looking!

Rob

[JohnJPS · Accepted solution]

Hi @danilang,

I played around with the data you provided, in the attached workflow. I split the concatenated list, had to remove the single-quotes, and then it joined OK.  Perhaps it's the stripping of the quotes that's required? (See attached).

Thanks,

John

[rob_lawson]

@JohnJPS 

Thanks but the quotes are necessary. The Concat list is getting pulled into an SQL query and this field is an AlphaNumeric Character field, which requires the tick mark to read.

I've found a different solution, which is much clunkier and not as "elegant" as this would have been, but it works.

I'll leave this open, for a day or two, to see if any other thoughts come up around why the Dynamic Input is not working on some of the deductions....

Thanks!

[JohnJPS]

Going back to @danilang's question, would it be possible to see the actual input to the join's R input?  We've seen the L input's actual data, but for the R input, we've only seen a process. I don't think Alteryx is failing to match perfectly identical values, so I'm still pretty convinced the data arriving at the R input is not "perfectly identical" for some reason, so the question becomes more of a question about the results of the dynamic input, more-so than a question about the Join failing to properly do its job.

(Anyway, so basically, whatever is going into the R input; drag another line from that source and send it straight to a .yxdb output for analysis.)

Thanks!

John