topic Re: I want to split my field values into two fields. in QlikView

I want to split my field values into two fields.

MuraliPrasath — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I just want to split my field values into two values.

FiscalYr.Period Year Period

2013000 into two fields 2013 000

Re: I want to split my field values into two fields.

sunny_talwar — Thu, 12 May 2016 10:31:17 GMT

Try this:

LOAD Left(FiscalYr.Period, 4) as Year,

Right(FiscalYr.Period, 3) as Period,

FiscalYr.Period

FROM ....

Re: I want to split my field values into two fields.

Thu, 12 May 2016 10:52:29 GMT

Use,

Left(FiscalYr.Period,4) as Year,

right(FiscalYr.Period,3) as Period

Regards,

Snehal Nabar

Re: I want to split my field values into two fields.

swuehl — Thu, 12 May 2016 11:32:02 GMT

I would probably also use string functions like Left() and Right() to separate the value into two parts, but to suggest another option, maybe

Div(FiscalYr.Period,1000) as Year

and

Mod(FiscalYr.Period,1000) as Period

[Or

Num(Mod(FiscalYr.Period,1000),'000') as Period

if you want to keep the three digit format]

BIG advantage if you need to account for early medieval periods:

LOAD

Div(FiscalYr.Period,1000) as Year,

Num(Mod(FiscalYr.Period,1000),'000') as Period,

FiscalYr.Period

INLINE [

FiscalYr.Period

800004

2013001

];

Re: I want to split my field values into two fields.

Peter_Cammaert — Thu, 12 May 2016 11:43:20 GMT

You don't have to change your script. It can be done in the UI like this:

Create two listboxes, and instead of a Field, select <Expression> from the list and enter these expressions:

=num(left(FiscalYr.Period, 4)) /* For the year part */

=num(right(FiscalYr.Period, 3)) /* For the Period */

If you want to keep leading zeroes, add a format string to the num() call. Like in

=num(right(FiscalYr.Period, 3), '000') /* For the Period */

Peter

Re: I want to split my field values into two fields.

sunny_talwar — Thu, 12 May 2016 12:09:53 GMT

BIG advantage if you need to account for early medieval periods:

You almost convinced me Stefan (not that it matters)

Re: I want to split my field values into two fields.

Anonymous — Thu, 12 May 2016 12:33:44 GMT

LOAD

subfield(FIELD,'-',1) as F1,

subfield(FIELD,'-',2) as F2,

subfield(FIELD,'-',3) as F3,

subfield(FIELD,'-',4) as F4,

...

FROM ...; try in this way