How to see if column contains list of values then create new column?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have a workflow with a list of Excel values that is to be used as a reference. The Excel file has 1 column called [Item Description] and has 346 rows of unique descriptions.
I have another main data table input (9,000 records) with the same [Item Description] column that I will be joining with this Excel file.
What I am trying to do is the following:
If the [Item Description] from the Excel file exists in the [Item Description] from the data table, then create a new column [Area] = "YES".
I have tried to accomplish this several ways but am struggling to get the output that I am looking for. Any help or suggestions would be greatly appreciated.
- Labels:
- Datasets
- Help
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You can take advantage of a Join in this scenario.
Essentially, anything that joins based on the description field would be in both datasets. From there, I've used a formula to create a field called Area with a value of Yes. Anything that didn't join from the main dataset would come out of the L or R output (it depends on which input was which). In my case, they came out of the R output since the full dataset was the R input. I used a union to stack what matched and what didn't match back together.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The issue I'm running into is that the reference file Item_Description column only contains a portion of the data contained in the main table.
For example, say the main table Item_Description is:
Apple Macbook Pro 15" 2015
The excel file Item_Description contains:
Apple
What I am trying to do is see which items in the excel file Item_Description match the main table description, and then pull the main table values (i.e. the full description)
I have tried the above two methods, but I have not been able to pull the full description. Do you have any suggestions?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for the reply! I tried this method did not work with my data - please see my comment below. Do you have any other suggestions for this scenario?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
