topic RE:subfield in QlikView

RE:subfield

Chanty4u — Mon, 06 Jul 2015 07:28:44 GMT

how i vil get year,month by using subfield function

i wil get ans lik 2015-06 0r 2015-jun

can any one help on this

Suresh

Re: RE:subfield

MayilVahanan — Mon, 06 Jul 2015 07:32:11 GMT

Try like this

LOAD *, SubField(date,'-', -1) as Month,SubField(date,'-',1) as Year Inline

[

date

2015-06

];

Re: RE:subfield

Mon, 06 Jul 2015 07:33:08 GMT

Try this,

Subfield('2015-06','-') as Year

SubField('2015-jun','-',2) as Month

Re: RE:subfield

PrashantSangle — Mon, 06 Jul 2015 07:33:33 GMT

Hi,

can you posted sample data from which you want above output.

Regards

Re: RE:subfield

Chanty4u — Mon, 06 Jul 2015 07:49:01 GMT

thanks all

its working fyn

Suresh

Re: RE:subfield

jonathandienst — Mon, 06 Jul 2015 07:55:27 GMT

Why use subfield? QV has a wealth of date functions.

Month(Date#('2015-06', 'YYYY-MM'))

Year(Date#('2015-06', 'YYYY-MM'))

Month(Date#('2015-JUN', 'YYYY-MMM'))

Year(Date#('2015-JUN', 'YYYY-MMM'))

Month() will produce dual values which will sort properly. String mangling will not.

Re: RE:subfield

jagan — Mon, 06 Jul 2015 07:59:04 GMT

Hi,

Try like this using date function

Month(Date#('2015-06', 'YYYY-MM')) for getting month

Year(Date#('2015-06', 'YYYY-MM')) for getting year

Month(Date#('2015-Jun', 'YYYY-MMM')) for getting month

Year(Date#('2015-Jun', 'YYYY-MMM')) for getting year

Always use Date related functions otherwise there will be issues in sorting .

Hope this helps you.

Regards,

Jagan.

Re: RE:subfield

Kushal_Chawda — Mon, 06 Jul 2015 08:02:00 GMT

try,

Month(Date#(Field, 'YYYY-MM'))

Year(Date#(Field, 'YYYY-MM'))

Month(Date#(Field, 'YYYY-MMM'))

Year(Date#(Field, 'YYYY-MMM'))

Re: RE:subfield

jagan — Mon, 06 Jul 2015 08:08:59 GMT

Hi,

If you got the Answer please close this thread by giving correct answer to the reply which helps you in finding the answer.

Regards,

Jagan.

Re: RE:subfield

qlikviewwizard — Mon, 06 Jul 2015 08:14:09 GMT

Use the below script.

Derive_Month_Field:

LOAD

Subfield(Month,'-', 2) AS MonthField;

LOAD * INLINE [

Month

2015-01

2015-02

2015-03

2015-04

2015-05

2015-06

2015-07

2015-08

2015-09

2015-10

2015-11

2015-12

];

Re: RE:subfield

tyagishaila — Mon, 06 Jul 2015 08:14:21 GMT

Hi,

You can also get it by using left() and right().

Re: RE:subfield

Chanty4u — Mon, 06 Jul 2015 08:15:18 GMT

tanq all.

Suresh Chanty

Re: RE:subfield

sasiparupudi1 — Mon, 06 Jul 2015 08:18:10 GMT

Table1:

LOAD

if (len(Subfield(Date,'-',2))>2,Month(Date#(Date, 'YYYY-MMM')),Month(Date#(Date, 'YYYY-MM'))) as Month,

if (len(Subfield(Date,'-',2))>2,Year(Date#(Date, 'YYYY-MMM')),Year(Date#(Date, 'YYYY-MM'))) as Year

Inline

[

Date

2002-01

2003-Apr

2014-02

2015-Jun

];

Re: RE:subfield

qlikviewwizard — Mon, 06 Jul 2015 08:20:06 GMT

Hi sureshqv ,

Please close the thread by selecting Correct Answer.

Re: RE:subfield

tyagishaila — Mon, 06 Jul 2015 08:20:12 GMT

left( [fieldName], 4) as Year

right( [fieldName],2) as Month