Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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".
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
MonthYear is only showing Month. I need to create MonthYear I think from the 3rd and 4th parts of the original field
This pulls though just Year, not the month with year
As trdandamudi would you be able to share 2-3 rows of your %join_terms field?
%Join_terms
Private-Company1-Jan-2015
Private-Company1-Feb-2016
Private-Company2-Mar-2016
Public-NHS1-Feb-2016
Public-NHS2-Apr-2015
change it to 2 instead 3..it will work
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