Dates column with year "2014" and "Unknown" What Data Type to choose for analysis?

Question

Hi All,

I have a merchant order data, there are two columns "Contract Star Date" and "Contract End Date". The Aim is to find the contract period.

Now, for some orders in  "Contract Start Date" Years are mentioned like 2014, 2015, etc. and wherever the values were not given "Unknown is mentioned"

Similarly in  "Contract End Date" Years are mentioned like 2014, 2015, etc. and wherever the values were not given "Unknown is mentioned"

Question: What datatype we should select for such columns? and How to calculate the contract period?

Below is the sample

Thanks in Advance.

Sample.xlsx

gmamtani · Answer

@davidskaife @binu_acs I am very new to Alteryx and I was struggling with this.

Thanks both, you have made my job a lot easier.

davidskaife · Answer

Hi @gmamtani

What data type do you want them to be? Alteryx will handle them as the default string type, and allow calculations with a little bit of tinkering:

The above is one example on how to approach this, it might be better if you provided details on your expected output and we can tailor a solution to that?

Contract Diff.yxmd

binu_acs · Answer

@gmamtani Since the column contains string type, Alteryx treat them all as string data type. I created a workflow for your reference, not sure is that something you are looking for

Contract_210223.yxmd