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.

AndrewBanh

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

Flagging draft and final.yxmd