topic Re: Regarding Dates? in QlikView

Regarding Dates?

Wed, 05 Aug 2015 18:02:53 GMT

HI everyone,

I have a small doubt in qlikview

in my month field :the month format is 2015M07 represents July 2007

i want the Month field as July only............

i.e.,

2015M07 to july

2015M06 to june

2015M05 to May

2015M04 to Apr

like that

Re: Regarding Dates?

Anonymous — Wed, 05 Aug 2015 21:07:06 GMT

hello,

if the your datefrmat indicates that the M stands for month you might use

=date(date#(keepchar('2015M07 ','0123456789'),'YYYYMM'),'MMM')

it first reduces your string to only numbers "201507"

then formats this string as date (the date#) function

and finally formats to monthname (but only 3 charatcers e.g. Jul)

if you want more charatcers you Need to adapt in sctipt to 4 or more characters

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

Re: Regarding Dates?

maxgro — Wed, 05 Aug 2015 21:15:04 GMT

=month(makedate(left('2015M07', 4), Right('2015M07', 2)))

replace '2015M07' with your field

Re: Regarding Dates?

Thu, 06 Aug 2015 03:26:10 GMT

Thnx Massimo,Dis is exactly wat iam looking for,

But

i want Month field to be "Month Format" Field

as per the above formula, im getting single field in text object,

Im missing something,PLs explain

Month as MonthFormat

2015M07 Jul

2015M06 Jun

2015M05 May

2015M04 Apr

Re: Regarding Dates?

Thu, 06 Aug 2015 03:27:14 GMT

Thnx Rudolf Linder ,

Dis is exactly wat iam looking for,

But

i want "Month" field to be "MonthFormat" Field

as per the above expression, im getting single field in text object,

Im missing something,PLs explain

Month as MonthFormat

2015M07 Jul

2015M06 Jun

2015M05 May

2015M04 Apr

Re: Regarding Dates?

sunny_talwar — Thu, 06 Aug 2015 03:32:16 GMT

Looking to get this?

Try this script:

Table:

LOAD Month,

Month(MakeDate(Left(Month, 4), Right(Month, 2))) as NewMonth;

LOAD * Inline [

Month

2015M07

2015M06

2015M05

2015M04

];

Re: Regarding Dates?

sasiparupudi1 — Thu, 06 Aug 2015 06:07:01 GMT

LOAD Month,

Month(Date#(PurgeChar(Month,'M'),'YYYYMM')) as MonthFormat;

LOAD * Inline [

Month

2015M07

2015M06

2015M05

2015M04

];

Re: Regarding Dates?

Kushal_Chawda — Thu, 06 Aug 2015 06:16:00 GMT

Try,

Data:

LOAD *,

Month(Date#(left(MonthText,4) & right(MonthText,2),'YYYYMM')) as Month,

Year(Date#(left(MonthText,4) & right(MonthText,2),'YYYYMM')) as Year,

monthname(Date#(left(MonthText,4) & right(MonthText,2),'YYYYMM')) as MonthYear

Inline [

MonthText

2015M07

2015M06

2015M05

2015M04

];

Re: Regarding Dates?

Thu, 06 Aug 2015 06:37:20 GMT

Hi Himesh,

Try like this,

month(makedate(left('2015M07',4), Right('2015M07',2))) as MonthName

take listbox and select this MonthName It display only months jul,jun...

Re: Regarding Dates?

Anonymous — Thu, 06 Aug 2015 06:47:00 GMT

it is still a date field and only formatted as string "monthname"

you can Format it to your liking

Re: Regarding Dates?

ashwanin — Thu, 06 Aug 2015 07:09:22 GMT

Hi,

Might be this is what you required.

Re: Regarding Dates?

maxgro — Thu, 06 Aug 2015 08:09:26 GMT

just replace the '2015M07' with your field Month

=month(makedate(left('2015M07', 4), Right('2015M07', 2)))

you can do it in the script

load

....

month(makedate(left(yourfield, 4), Right(yourfield, 2))) as NewMonth

....

or in the user interface (chart)