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

[sunny_talwar]

May be this:

Market_Terms:

LOAD

    %join_terms,

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

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

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

    Date(MonthStart(Date#(Subfield (%join_terms, '-',3), 'WhateverFormatTheMonthYearFieldIsIn')), 'WhateverFormatYouWantThisNewFieldToBeIn') as MonthStart

[trdandamudi]

Can you please share some sample data. What are the values in %join_terms ? The function MonthName() will give both month and year like "June 2016".

[Kushal_Chawda]

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,'-',3)) AS MonthYear,

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

Note - Assuming Month-Year values like Jan-2015.. change it according to the values

[kevbrown]

MonthYear is only showing Month. I need to create MonthYear I think from the 3rd and 4th parts of the original field

[kevbrown]

This pulls though just Year, not the month with year

[sunny_talwar]

As trdandamudi‌ would you be able to share 2-3 rows of your %join_terms field?

[kevbrown]

%Join_terms

Private-Company1-Jan-2015

Private-Company1-Feb-2016

Private-Company2-Mar-2016

Public-NHS1-Feb-2016

Public-NHS2-Apr-2015

[Kushal_Chawda]

change it to 2 instead 3..it will work

[Kushal_Chawda]

Market_Terms:

LOAD

    %join_terms,

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

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

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

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