Split a field then join

[kevbrown]

I have a field that consists of Sector-Company-Month-Year and i need to split it out to Sector, Company, MonthYear, so 3 new fields. I also want to create a 4th field which will be the 1st day of each MonthYear combination.

So far I have

Market_Terms:

LOAD

    %join_terms,

    Subfield (%join_terms, '-',1) AS Sector,

    Subfield (%join_terms, '-',2) AS Company,

    Subfield (%join_terms, '-',3) AS MonthYear

However MonthYear s just showing Month

[kevbrown]

That works to an extent but shows -Jan-2016 and MonthYear is blank

[Kushal_Chawda · Accepted solution]

try this

Market_Terms:

LOAD

    %join_terms,

    Subfield (%join_terms, '-',1) AS Sector,

    Subfield (%join_terms, '-',2) AS Company,

   mid(%join_terms,index(%join_terms,'-',2)+1) AS MonthYear,

    monthstart(date#(mid(%join_terms,index(%join_terms,'-',2)+1),'MMM-YYYY')) as FirstDayMonth

[kevbrown]

perfect, thank you

[Kushal_Chawda]

welcome