Indicating whether a column contains two values, for a given entry in a different 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
Hello Community! I am working with a dataset that looks like the following:
Entity Draft_Final Versions
A Draft
A Final
A Draft
B Draft
C Final
D Final
E Draft
E Final
I am trying to write a formula (and potentially another column) that indicates whether a given entity has both "Draft" and "Final" versions.
The new dataset might look something like the following:
Entity Draft_Final Versions Has_Draft_and_Final
A Draft 1
A Final 1
A Draft 1
B Draft 0
C Final 0
D Final 0
E Draft 1
E Final 1
I'm not sure if this is the right way to indicate that the entity has both draft and final versions, so if anyone has ideas on how to write the formula and/or a better way to create a tag of an entity having both draft and final versions, please let me know! Your help is greatly appreciated.
Solved! Go to Solution.
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @vanorve
Here's a solution:
- Summarize tool to count distinct values for each entity
- Use Formula tool to subtract 1 from the count distinct value
- Use Join Tool to bring these values to the dataset
WF attached.
Cheers,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @vanorve
I can see that @Thableaus has done most of the heavy lifting already, but my suggestion is just slightly different.
When you are looking for specific strings (ie. "Draft" and "Final") I would suggest to use a formula to flag those specific string(s).
I would still summarise and group on Entity white concatenating on "Draft_Final Versions" and then use the formula tool using the expression below:
if Contains([Concat_Draft_Final Versions], 'Draft', 'Final')
then 1
else 0
endif
I have attached the workflow for you :)
Hope this helps Vanorve!
- Andrew
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you so much @Thableaus !
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you so much, @AndrewBanh !
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You're welcome @vanorve
