Indicating whether a column contains two values, for a given entry in a different column

[vanorve]

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.

[Thableaus]

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,

[AndrewBanh · Accepted solution]

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

[vanorve]

Thank you so much @Thableaus !

[vanorve]

Thank you so much, @AndrewBanh ! 

[Thableaus]

You're welcome @vanorve