Hi, I need to create a new field based on other fields and multiple conditions. I want to map 'Quarter' as either 'Q1' or 'Q2' or 'Q3' based on the date and company specific period. The challange here is date should not be taken into account as calendar date. Otherwise, it would have been pretty straightforward as March, June and Spetember would have been Q1, Q2 and Q3 respectively. 'Period' field helps us to identify year-end. For example if December is 'Year' then December is year-end and March is first quarter (Q1). On the other hand, if September is 'Year' for a company then September is year-end and December is first quarter (Q1) and March is second quarter (Q2) for that company. Below is a sample illustrative data I created. I need to create Quarter (th) column. As you can see: * Company 2 has December as 'Year', then March ıs Q1 * Company 7 has December as 'Year', then March is Q1 and September is 'Q3' * Company 313 has March as 'Year', then June is Q1 * Company 322 has September as 'Year', then December is Q1, March is Q2 and June is Q3 CompanyDatePeriodQuarter (th)Company231.12.2016Year Company230.06.2017Half-Year Company231.12.2017Year Company230.06.2018Half-Year Company231.12.2018Year Company231.03.2019QuarterQ1Company230.06.2019Half-Year Company231.12.2019Year Company230.06.2020Half-Year Company631.12.2017Year Company630.06.2018Half-Year Company631.12.2018Year Company631.03.2019QuarterQ1Company630.06.2019Half-Year Company631.12.2019Year Company631.03.2020Quarter Company630.06.2020Half-Year Company731.12.2016Year Company731.03.2017QuarterQ1Company730.06.2017Half-Year Company730.09.2017QuarterQ3Company731.12.2017Year Company731.03.2018QuarterQ1Company730.06.2018Half-Year Company730.09.2018QuarterQ3Company731.12.2018Year Company731.03.2019QuarterQ1Company730.06.2019Half-Year Company730.09.2019QuarterQ3Company731.12.2019Year Company731.03.2020QuarterQ1Company730.06.2020Half-Year Company6431.03.2018Quarter Company6430.06.2018Quarter Company6431.12.2018Year Company6431.03.2019QuarterQ1Company6430.06.2019QuarterQ2Company6430.09.2019QuarterQ3Company6431.12.2019Year Company6431.03.2020QuarterQ1Company6430.06.2020QuarterQ2Company6430.09.2020QuarterQ3Company7431.12.2017Year Company7431.12.2018Year Company7431.03.2019QuarterQ1Company7430.06.2019QuarterQ2Company7430.09.2019QuarterQ3Company7431.12.2019Year Company7431.03.2020QuarterQ1Company7430.06.2020QuarterQ2Company7430.09.2020QuarterQ3Company7731.12.2017Year Company7731.03.2018QuarterQ1Company7730.06.2018Half-Year Company7730.09.2018YTD Company7731.12.2018Year Company7730.09.2019YTD Company7731.12.2019Year Company9831.12.2017Year Company9831.12.2018Year Company9831.03.2019QuarterQ1Company9830.06.2019QuarterQ2Company9830.09.2019QuarterQ3Company9831.12.2019Year Company9831.03.2020QuarterQ1Company9831.12.2020Year Company31331.03.2017Year Company31331.03.2018Year Company31331.03.2019Year Company31330.06.2019QuarterQ1Company31331.03.2020Year Company31330.06.2020QuarterQ1Company32230.09.2016Year Company32231.03.2017QuarterQ2Company32230.09.2017Year Company32231.12.2017QuarterQ1Company32231.03.2018QuarterQ2Company32230.06.2018QuarterQ3Company32230.09.2018Year Company32231.12.2018QuarterQ1Company32231.03.2019QuarterQ2Company32230.06.2019QuarterQ3Company32230.09.2019Year Company32231.12.2019QuarterQ1Company32231.03.2020Half-Year Company32230.06.2020QuarterQ3Company32230.09.2020Year Thanks a lot

Calculated field based on multiple fields and conditions

Hi,

I need to create a new field based on other fields and multiple conditions. I want to map 'Quarter' as either 'Q1' or 'Q2' or 'Q3' based on the date and company specific period. The challange here is date should not be taken into account as calendar date. Otherwise, it would have been pretty straightforward as March, June and Spetember would have been Q1, Q2 and Q3 respectively. 'Period' field helps us to identify year-end. For example if December is 'Year' then December is year-end and March is first quarter (Q1). On the other hand, if September is 'Year' for a company then September is year-end and December is first quarter (Q1) and March is second quarter (Q2) for that company. Below is a sample illustrative data I created. I need to create Quarter (th) column.

As you can see:

Company 2 has December as 'Year', then March ıs Q1
Company 7 has December as 'Year', then March is Q1 and September is 'Q3'
Company 313 has March as 'Year', then June is Q1
Company 322 has September as 'Year', then December is Q1, March is Q2 and June is Q3

Company	Date	Period	Quarter (th)
Company2	31.12.2016	Year
Company2	30.06.2017	Half-Year
Company2	31.12.2017	Year
Company2	30.06.2018	Half-Year
Company2	31.12.2018	Year
Company2	31.03.2019	Quarter	Q1
Company2	30.06.2019	Half-Year
Company2	31.12.2019	Year
Company2	30.06.2020	Half-Year
Company6	31.12.2017	Year
Company6	30.06.2018	Half-Year
Company6	31.12.2018	Year
Company6	31.03.2019	Quarter	Q1
Company6	30.06.2019	Half-Year
Company6	31.12.2019	Year
Company6	31.03.2020	Quarter
Company6	30.06.2020	Half-Year
Company7	31.12.2016	Year
Company7	31.03.2017	Quarter	Q1
Company7	30.06.2017	Half-Year
Company7	30.09.2017	Quarter	Q3
Company7	31.12.2017	Year
Company7	31.03.2018	Quarter	Q1
Company7	30.06.2018	Half-Year
Company7	30.09.2018	Quarter	Q3
Company7	31.12.2018	Year
Company7	31.03.2019	Quarter	Q1
Company7	30.06.2019	Half-Year
Company7	30.09.2019	Quarter	Q3
Company7	31.12.2019	Year
Company7	31.03.2020	Quarter	Q1
Company7	30.06.2020	Half-Year
Company64	31.03.2018	Quarter
Company64	30.06.2018	Quarter
Company64	31.12.2018	Year
Company64	31.03.2019	Quarter	Q1
Company64	30.06.2019	Quarter	Q2
Company64	30.09.2019	Quarter	Q3
Company64	31.12.2019	Year
Company64	31.03.2020	Quarter	Q1
Company64	30.06.2020	Quarter	Q2
Company64	30.09.2020	Quarter	Q3
Company74	31.12.2017	Year
Company74	31.12.2018	Year
Company74	31.03.2019	Quarter	Q1
Company74	30.06.2019	Quarter	Q2
Company74	30.09.2019	Quarter	Q3
Company74	31.12.2019	Year
Company74	31.03.2020	Quarter	Q1
Company74	30.06.2020	Quarter	Q2
Company74	30.09.2020	Quarter	Q3
Company77	31.12.2017	Year
Company77	31.03.2018	Quarter	Q1
Company77	30.06.2018	Half-Year
Company77	30.09.2018	YTD
Company77	31.12.2018	Year
Company77	30.09.2019	YTD
Company77	31.12.2019	Year
Company98	31.12.2017	Year
Company98	31.12.2018	Year
Company98	31.03.2019	Quarter	Q1
Company98	30.06.2019	Quarter	Q2
Company98	30.09.2019	Quarter	Q3
Company98	31.12.2019	Year
Company98	31.03.2020	Quarter	Q1
Company98	31.12.2020	Year
Company313	31.03.2017	Year
Company313	31.03.2018	Year
Company313	31.03.2019	Year
Company313	30.06.2019	Quarter	Q1
Company313	31.03.2020	Year
Company313	30.06.2020	Quarter	Q1
Company322	30.09.2016	Year
Company322	31.03.2017	Quarter	Q2
Company322	30.09.2017	Year
Company322	31.12.2017	Quarter	Q1
Company322	31.03.2018	Quarter	Q2
Company322	30.06.2018	Quarter	Q3
Company322	30.09.2018	Year
Company322	31.12.2018	Quarter	Q1
Company322	31.03.2019	Quarter	Q2
Company322	30.06.2019	Quarter	Q3
Company322	30.09.2019	Year
Company322	31.12.2019	Quarter	Q1
Company322	31.03.2020	Half-Year
Company322	30.06.2020	Quarter	Q3
Company322	30.09.2020	Year

Thanks a lot

Transformation

Preparation

Accepted answers

AngelosPachis

Hi @onanm ,

Maybe something like this? I have annotated the steps in the workflow to guide you through it

Hope that helps,

Angelos

example.yxmd

All comments

Qiu

@onanm
Hope this works for you.

0328-onanm.yxmd

AngelosPachis

Hi @onanm ,

Maybe something like this? I have annotated the steps in the workflow to guide you through it

Hope that helps,

Angelos

example.yxmd

onanm

Thanks a lot Angelos!

It worked well. However, I have slightly modified the workflow as I noticed that some companies in my dataset have multiple year-ends identified and some companies have not even year-end identified. In order to overcome former issue, I added an intermediary step to forcing year-end as maximum date having 'year' period. As a result, I have got unique year-end month for each company. Latter issue has been overcome by left outer join by having union of L and J.

Thanks again

onanm

Thanks a lot Qio,

Though it worked well, I employed a bit more convenient approach proposed by Angelos. Besides, some companies do no have unique year-end months and some companies even do not have year-end month identified. I figured out these issues.

Thanks again for your kind help

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335