topic Re: Extracting Quarter from date field in QlikView

Extracting Quarter from date field

Fri, 27 Jun 2014 04:26:08 GMT

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

Re: Extracting Quarter from date field

aveeeeeee7en — Fri, 27 Jun 2014 04:42:57 GMT

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

Re: Extracting Quarter from date field

Fri, 27 Jun 2014 04:56:27 GMT

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)

Re: Extracting Quarter from date field

Fri, 27 Jun 2014 04:57:00 GMT

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.

Re: Extracting Quarter from date field

Fri, 27 Jun 2014 05:10:37 GMT

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)

Re: Extracting Quarter from date field

CELAMBARASAN — Fri, 27 Jun 2014 05:12:54 GMT

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;

Re: Extracting Quarter from date field

aveeeeeee7en — Fri, 27 Jun 2014 05:13:00 GMT

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.

Re: Extracting Quarter from date field

aveeeeeee7en — Fri, 27 Jun 2014 05:15:54 GMT

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;