Creating formula for dimension

43918084 — Mon, 20 Dec 2021 21:08:46 GMT

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

Re: Creating formula for dimension

MayilVahanan — Thu, 12 Nov 2020 03:47:14 GMT

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

Re: Creating formula for dimension

43918084 — Thu, 12 Nov 2020 05:21:23 GMT

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.

topic Re: Creating formula for dimension in App Development

Creating formula for dimension

Re: Creating formula for dimension

Re: Creating formula for dimension