topic Re: how can I split a field into two? in QlikView

how can I split a field into two?

Tue, 07 Jul 2015 13:04:51 GMT

LOAD

GEO&'.'&[Operating Area]&'.'&[Sales_Region]&'.'&Territory&'|'&upper(ACCOUNT_SEGMENT)&'|'&[NCV_Type]&'.'&[Hyperion Budget Unit]&'.'&SCBU&'|'&left(Fiscal_Period,4)&'-Q'&Right(Fiscal_Period,1)

as [~MasterKey],

Pipe,

'PNCV' AS [NCV_Type]

FROm

(ooxml, embedded labels, table is Data);

this is the code , but the fiscal period is in the image below,

i want this to be like 2016 as Fiscal Year and 1 as Q1, Q2,.. as Fiscal quarter.
I want this to be scripted in the script above in the master key.
But the data which I am getting it only has Fiscal period column, I want to split it as fiscal year and fiscal quarter and add that into the master key script.
How can this be achieved?
Thanks in advance.

Re: how can I split a field into two?

alexandros17 — Tue, 07 Jul 2015 13:07:41 GMT

Load

...

Left(Fiscal_Period, 4) as fiscal_year,

Right(Fiscal_Period, 1) as Fiscal_Quarter,

....

Re: how can I split a field into two?

sunny_talwar — Tue, 07 Jul 2015 13:07:47 GMT

You can split it like this in the script:

Left(Fiscal_Period, 4) as FiscalYear,

Right(Fiscal_Period, 1) as FiscalQuarter

Re: how can I split a field into two?

avinashelite — Tue, 07 Jul 2015 13:08:36 GMT

try like this:

LOAD

subfield(Fiscal_period,' ',1) as Fiscal_Year,

'Q'&subfield(Fiscal_period,' ',2) as Fiscal_Quater

from

table ;

Re: how can I split a field into two?

Anonymous — Tue, 07 Jul 2015 13:10:10 GMT

I assume that you want 'Q' in fiscal quarter, so

'Q' & right(Fiscal_Period, 1) as Fiscal_Quarter,

Re: how can I split a field into two?

alexandros17 — Tue, 07 Jul 2015 14:24:25 GMT

My answer was even correct ...