Extracting Quarter from date field

[#1]

Hi,

I have field called Quarter_ID which is in teh format yyyy1,yyyy2,yyyy3,yyyy4. So now i wanted to replace the quarter numbers and assign them a new values like below : (field : Q_Replace)

1 -> Q1

  2 -> Q2

  3 -> Q3

  4 -> Q4.

  And also want one more field where i want the quater with the only replaced values (field : Q_Extract)

My Quarter_ID field is like below:

Quarter_ID
20111
20114
20123
20133
20132

Expected output is :

Quarter_ID	Q_Replace	Q_Extract
20111	2011Q1	Q1
20114	2011Q4	Q4
20123	2012Q3	Q3
20133	2013Q3	Q3
20132	2013Q2	Q2

So how this can be done. So that in my report i can show by quarter wise details.

Thanks

[aveeeeeee7en]

Hi Shree

Try this code:

LOAD Quarter_ID,

     Left(Quarter_ID,4)&'Q'&Right(Quarter_ID,1) AS Q_Replace,

     'Q'&Right(Quarter_ID,1) AS Q_Extract

FROM

\Desktop\Excel.xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards

Aviral Nag

[#3]

use right(quarter_id,1) as quarter no(with this u will get 1,2,3,4)

and use left(quarter_id,4 ) as year(with this u will get year)

and then use inline

for quarter no,quarter

1,q1

2,q2

3,q3

4,q4

with this u will get quarter in q1,q2,q3,q4)

by using year&-&quarter

(with this u will get year with quarter in format q1,q2,q3,q4)

[#4]

Hi Aviral Nag, i can not view the contents of your attachment as i'm using personal edition.

Also i' using hive as my datasource so i i have only SELECT query not any LOD statement.

[#5]

My , SELECT script is like below:

SQL SELECT *

FROM HIVE.orderdetails.daytable;

So how can i write LOAD statement to the above query ? (My Querter_ID field resides in the daytable table)

[CELAMBARASAN]

Assuming that you have a select query

SQL Select Quarter_ID

From TableName;

Try

Load

     Quarter_ID,

     Text(Date(Formatted, 'YYYYQM')) AS Q_Replace,

     Text(Date(Formatted, 'QM')) AS Q_Extract;

Load

     Quarter_ID,

     Date(Date#(Quarter_ID, 'YYYYM')) AS Formatted;

SQL Select Quarter_ID

From TableName;

[aveeeeeee7en]

I have also shared with you the Script.

Just copy it and paste it in your Personal Edition Edit Script and change the Data Source and location.

Finally, Reload the Application.

It will work for you.

[aveeeeeee7en]

May be like this:

Main:

SQL SELECT *

FROM HIVE.orderdetails.daytable;

Noconcatenate

Final:

LOAD Quarter_ID,

     Left(Quarter_ID,4)&'Q'&Right(Quarter_ID,1) AS Q_Replace,

     'Q'&Right(Quarter_ID,1) AS Q_Extract

Resident Main;

DROP Table Main;