topic Re: Regarding Month Field in QlikView

Regarding Month Field

Thu, 05 Sep 2013 05:56:12 GMT

Hi All,

i have loaded data from Oracle Database as follows

SELECT

D_NR,

REGION,

REGION_DESC,

CNTRY_NM,

SUB_REGION_CD,

SUB_REGION_DESC,

W_L_PROB,

W_L_STAT_CD,

BEG_DT,

year(BEG_DT) as year,

month(BEG_DT) as month,

END_DT

FROM DEAL_DN where year(BEG_DT)='2013';

My problem is it's loading months as 1,2,3,4,5,6,7,8,9,10,11,12, But i want to load as Jan, Feb, Mar...........

i used "date(BEG_DT,'MMM') as Month", "Month( Date#( Month,'MMM') ) as Month " also but i got error

and While creating Quarter field using " 'Q' & Ceil(Month("ORDER_BGN_DT")/3) as Quarter " i'm getting error and RELOAD is failed. I attached the error Screenshots also.

Please can anyone look into this issue and give me solution.

Thanks,

MV Reddy

Re: Regarding Month Field

tresB — Thu, 05 Sep 2013 06:04:53 GMT

You can't use qv functions under SQL command. You have to load the data into qlikview and then you can use qv functions like:

Load

year(BEG_DT) as year,

month(BEG_DT) as month ;

SQL Select * from <>;

Re: Regarding Month Field

kangaroomac — Thu, 05 Sep 2013 06:05:07 GMT

Hiya,

For the month, try:

Month(Date(BEG_DT, 'DD/MM/YYYY'))

Change the DD/MM/YYYY to whatever your date format is.

Hope this helps.

Re: Regarding Month Field

Thu, 05 Sep 2013 11:16:55 GMT

Hi Tresesco,

Thanks for your Reply, i did it using "to_char("BEG_DT", 'mon') as Month".

But Order of months is like apr,aug,dec,feb,jul,jun.....

But i want like jan, feb, mar, apr, may, jun.........dec as below screen

Please give me some idea on this?

Thanks,

Vamsi.

Re: Regarding Month Field

Thu, 05 Sep 2013 11:17:31 GMT

Hi All,

i did it using "to_char("BEG_DT", 'mon') as Month".

But Order of months is like apr,aug,dec,feb,jul,jun.....

But i want like jan, feb, mar, apr, may, jun.........dec as below screen

Please give me some idea on this?

Thanks,

Vamsi.

Re: Regarding Month Field

tresB — Thu, 05 Sep 2013 11:27:33 GMT

Hi Vamsi,

That means it is being treated as text. Better you convert this text to date format during load. Try like this:

Load Date#(Month,'MMM') as Month