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

Nested Search based on multiple sub categories

RakeshKoppuravuri
7 - Meteor

HI,

 

We have got two files one with column description and another with multiple Categories(Look up). We need to search for the strings available in the multiple category columns. The subset from the first search, is then searched with the second category and so on. Could anybody suggest the best approach to do so.

 

Look up File with CategoriesLook up File with CategoriesDescription FileDescription File

 

I need to take the combination of C,D & E column(not Concatenation) from lookup file and search any where in the description column and pull value1, value 2 and value 3 with which ever combination the description got match.

9 REPLIES 9
MarqueeCrew
20 - Arcturus
20 - Arcturus

@RakeshKoppuravuri,

 

 

If all of your Descriptions are constant, you can create Desc1 and Desc2 fields by taking the first word the last word.  Then you can join your search descriptions to the value data.

 

GetWord([Description],0)
-------------------------
GetWord([Description],3)

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
RakeshKoppuravuri
7 - Meteor

HI Marquee,

 

In my case description is not constant, the words can be present any where in the description.

MarqueeCrew
20 - Arcturus
20 - Arcturus

@RakeshKoppuravuri,

 

If you put a recordID on each description input, you can use the Text to columns tool to tokenize each word by using a space (\s) delimiter and splitting to rows.

 

1|Fruits

1|available

1|are

1|Banana

2|Fruits 

....

 

If you take the contents of your other file and place a recordID on it and simplify it to:  RecordID + Product Name & Sub Category you can join the data so that you find where you get matches on the description from both Product and Category.

 

Capture.png

 

I've included a yxmd that takes you through the final matching process (after you've parsed the inputs).

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
RakeshKoppuravuri
7 - Meteor

Hi Marquee,

 

I think yxmd attachment is missed in your post. Could you please check and attach the same.

 

Just to reiterate the process after the searching on combination in the description, i need to pull the value1 , value2 and value 3 with what ever combination it got matched from category file and has to put against to the description .


@MarqueeCrew wrote:

@RakeshKoppuravuri,

 

If you put a recordID on each description input, you can use the Text to columns tool to tokenize each word by using a space (\s) delimiter and splitting to rows.

 

1|Fruits

1|available

1|are

1|Banana

2|Fruits 

....

 

If you take the contents of your other file and place a recordID on it and simplify it to:  RecordID + Product Name & Sub Category you can join the data so that you find where you get matches on the description from both Product and Category.

 

Capture.png

 

I've included a yxmd that takes you through the final matching process (after you've parsed the inputs).

 

Cheers,

Mark


 

RakeshKoppuravuri
7 - Meteor

Just to add to my previous reply, even i have combination like where value is present for Product name  and empty for subcategory2.

 

even we need to consider that as a valid.

MarqueeCrew
20 - Arcturus
20 - Arcturus

@RakeshKoppuravuri,

 

I've provided you with a couple of approaches based upon your initial ask.  I recommend that you try customizing them to your data and trap conditions where these rules either fail to match or match with bad results.  You can then approach the challenge looking for improvements.  There isn't a single tool available in Alteryx suited for your exact business problem.  Configuration of a process that matches your needs is achievable with Alteryx.

 

I can see potential issues with spelling, abbreviations, missing data just to mention a few things that might go wrong.  If this work is something that you've already performed in another tool-set or via a manual process, walking through that process should help in translation to a repeatable process.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
RakeshKoppuravuri
7 - Meteor

Thanks for your suggestion Marquee

 

Could you please post the workflow of which you developed and posted screenshot.

jrgo
14 - Magnetar

@RakeshKoppuravuri,

 

While not the simplest tool to work with, this may be a use case to use Fuzzy Matching, especially (to @MarqueeCrew previous comment) if your string values may contain misspellings.

There's a one-tool example available to give you an overview of the tool and it's concepts. There's also pre-recorded live trainings on this subject on Alteryx's training section.

image.png

 

Best,

 

Jimmy

MarqueeCrew
20 - Arcturus
20 - Arcturus

here is the workflow as requested.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels