ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Analytics Hub

Find answers, ask questions, and share expertise about Alteryx Analytics Hub.
SOLVED

Search for matching name in another column and seperate the data found

hchepyal
6 - Meteoroid

Hello Everyone,

I am a newbie to Alteryx Designer. I have a question regarding the data matching. For Example

Let us say I have two tables

 

GetRequest

SourceDestinationDescription
hostDevice_1GetRequest
hostDevice_2GetRequest
hostDevice_3GetRequest
hostDevice_4GetRequest

 

 

GetResponse

SourceDestinationDescription
Device_2hostGetResponse
Device_1hostGetResponse
Device_4hostGetResponse

 

I want to know what devices are sending the 'GetResponse' after sending the 'GetRequest' and seperate the devices that are not sending.

 

Please let me know if you have a possible solution

 

Thanks

 

Hari 

MattBSlalom
10 - Fireball

If I understand you correctly, you're looking for the Join tool.  It takes to datasets as inputs and then you configure what fields between them should be the keys used to lookup between them.

 

In your case, the configuration would be "Source" from "GetRequest" dataset is equal to "Destination" from "GetRepose" dataset, and as a second condition, "Destination" from "GetRequest" dataset is equal to "Source" from "GetRepose" dataset.

 

From Join tool Help (Join Tool | Alteryx Help😞

The 3 outputs that result from the join are...

L anchorContains records from the L input that didn't join to records from the R input.
J anchorContains records that joined from the L input to the records in the R input.
R anchorContains records from the R input that didn't join to records from the L input.

 

So, in your case, the J output anchor will have all Requests that found a matching Response, and the L & R output anchors will provide the Requests without a matching Response, and (if possible) and Responses without a matching Request.

hchepyal
6 - Meteoroid

Thanks Matt,

 

I tried 'Join by position', it would join the data but the data in one column does not match the data in the other column. Then I tried the method you suggested 'Join by Specific field' with the options you gave, it would not join the data with the fields specified. I am not sure what the problem is.

 

Regards,

 

Hari

MattBSlalom
10 - Fireball

I'm not sure what you have in your workflow, but it works as described for me.  See the attached sample.

hchepyal
6 - Meteoroid

I am sharing my workflow and a test data. Could you please let me know where I was going wrong?

MattBSlalom
10 - Fireball

You have 60 rows coming into this filter tool:

 

MattBSlalom_0-1619710167894.png

 

None of those rows are evaluating to True.

 

So your T output anchor has no data flowing through it; therefore, both of your Joins have no data for their L input anchors.

 

hchepyal
6 - Meteoroid

Forgot to change the hostname to 'ito022614.hosts.cloud.com' in the filter that I sent to you. Even after changing it, the join condition is not satisfying

MattBSlalom
10 - Fireball

Ok, I see.  You've parsed out these fields from the log file, but most still have trailing and/or leading spaces.  When the Join is performed, it's looking for an exact value match which includes any whitespace characters.

 

If you Browse your data, you'll see it warning about trailing & leading spaces with the Red triangle in the upper right corner of the cell & the Red bar under the Header of the field.  Hovering over a cell will give you a tool tip with info about the whitespace:

 

MattBSlalom_0-1619711826421.png

 

 

 

 

To solve this just drop in a Data Cleansing tool after you have your fields parsed out before your flow starts splitting.

 

MattBSlalom_0-1619711502268.png

 

hchepyal
6 - Meteoroid

Thank you Matt,

 

That helped solve the problem.

 

Regards,

Hari