Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Join failing on Left Side

rob_lawson
7 - Meteor

GMI WF-SnagIt.png

 

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

 

 

GMI WF-SnagIt #2.png

 

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?

 

 

 

7 REPLIES 7
JohnJPS
15 - Aurora

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

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
19 - Altair
19 - Altair

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

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.

 

GMI WF-SnagIt #4.png 

 

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
15 - Aurora

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

@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
15 - Aurora

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

 

Labels