How to derive Month_Year field from only Year and Quarter fields

[vishalmanu]

HI,

I have two fields with the below values.

Year

2016

2017

Quarter_year

2016 Q1

2016 Q2

2016 Q3

2016 Q4

2017 Q1

2017 Q2

2017 Q3

I want to derive a Month_year field for the respective quarters like Jan 2016, Feb 2016, Mar 2016 for 2016 Q1 and so on..

Kindly help with the solution

Thanks         

[chinnuchinni]

wirh out date field  do you want to "year month " from "Quarter_year"?

[vishalmanu]

Yes

[chinnuchinni]

As per my knowledge that will not possible.

if want to display, you can display like this.

[chinnuchinni]

Qyear:

LOAD *,if(Quater_Year ='2016 Q1',left(Quater_Year,4)& 'Jan ' &left(Quater_Year,4)& ' Feb '& left(Quater_Year,4)& ' Mar ',

if(Quater_Year ='2016 Q2',left(Quater_Year,4)& 'Apr ' &left(Quater_Year,4)& ' May '& left(Quater_Year,4)& ' Jun ',

if(Quater_Year ='2016 Q3',left(Quater_Year,4)& 'Jul ' &left(Quater_Year,4)& ' Aug '& left(Quater_Year,4)& ' Sep ',

if(Quater_Year ='2016 Q4',left(Quater_Year,4)& 'Oct ' &left(Quater_Year,4)& ' Nov '& left(Quater_Year,4)& ' Dec '

))))

as YearMonth1 INLINE [

    Quater_Year

    2016 Q1,

    2016 Q2,

    2016 Q3,

    2016 Q4

    2017 Q1

    2017 Q2

    2017 Q3

];

[vineetadahiya12]

Hi Vishnu,

Check if this suffice your requirement

Qtr_Month:

LOAD * INLINE [

    Qtr, Month

    Q1, Jan

    Q1, Feb

    Q1, Mar

    Q2, Apr

    Q2, May

    Q2, Jun

    Q3,Jul

    Q3,Aug

    Q3,Sep

    Q4,Oct

    Q4,Nov

    Q4,Dec

];

Year:

LOAD * INLINE [

    Year

    2016

    2017

];

Qtr_Yr1:

LOAD * INLINE [

    Qtr_Yr

    2016 Q1

    2016 Q2

    2016 Q3

    2016 Q4

    2017 Q1

    2017 Q2

    2017 Q3

];

left Join(Qtr_Month)

Qtr_yr:

Load Left(Trim(Qtr_Yr),4)as  Year, Right(Trim(Qtr_Yr),2) as Qtr , Qtr_Yr

Resident Qtr_Yr1;

Drop Table Qtr_Yr1;

NoConcatenate

Final:

Load Month & ' '&Year as Month_Yr, Year ,Qtr ,Qtr_Yr ,1

Resident Qtr_Month;

Drop Table Qtr_Month;

[krishna_2644]

Check this out:

[vishalmanu]

Hi Krishna,

Can you show the work around.

Thanks

VIshnu T

[krishna_2644]

Hi - this is the generic one.so you can use it with inline or from any table no matter what the year is.