how can I split a field into two?

[#1]

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.

[alexandros17 · Accepted solution]

Load

...

Left(Fiscal_Period, 4) as fiscal_year,

Right(Fiscal_Period, 1) as Fiscal_Quarter,

....

[sunny_talwar]

You can split it like this in the script:

Left(Fiscal_Period, 4) as FiscalYear,

Right(Fiscal_Period, 1) as FiscalQuarter

[avinashelite]

try like this:

LOAD

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

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

from

table ;

[Anonymous]

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

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

[alexandros17]

My answer was even correct ...