Creating formula for dimension

[43918084]

I have below sample data.  Blue fields are directly loaded from data source  of 2 different accounting period.  Some Deal number will exist in bother accounting period.  Some Deal number will exist in one of the accounting period.

I want to create a dimension "Status" to determine whether that deal number exist in both or just one of the accounting period.

The right hand side is my logic.  I am not sure how to create this dimension after data load, or if I should create it within the data script.

Hope I can have some guidance on this.

Data	Data	Data	Data	Data	require formula/Script
Deal number	Trad date	Maturity Date	Acct Date	Amt	Status	Logic
1	20180314	20230316	20200831	1,000	exist	if maturity date > latest acct Date, status is exist
1	20180314	20230316	20200930	500	exist	if maturity date > latest acct Date, status is exist
2	20180314	20200805	20200831	10	Matured	if maturity date < latest acct Date, status is Matured
3	20200902	20230316	20200930	20	New	If trade date > Latest acct date, status is New

[MayilVahanan · Accepted solution]

HI @43918084 

You can create a field in script 
Try like this

If(Trad Date > Acct Date, 'New',  if(Maturity Date > Acct Date, 'exist', 'Matured')) as Status

[43918084]

Sorry, I also need to create a field to return 2 if the deal number exist in bother accounting period and 1 if exist in one of the period.

May I know how I can build it in the script  as the line are separated in 2 tables?

Thank you very much in advance again.