topic Re: Split a field then join in QlikView

Split a field then join

kevbrown — Thu, 30 Jun 2016 12:40:41 GMT

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

Re: Split a field then join

sunny_talwar — Thu, 30 Jun 2016 12:44:19 GMT

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

Re: Split a field then join

trdandamudi — Thu, 30 Jun 2016 12:45:22 GMT

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".

Re: Split a field then join

Kushal_Chawda — Thu, 30 Jun 2016 12:47:53 GMT

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

Re: Split a field then join

kevbrown — Thu, 30 Jun 2016 12:51:05 GMT

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

Re: Split a field then join

kevbrown — Thu, 30 Jun 2016 12:52:52 GMT

This pulls though just Year, not the month with year

Re: Split a field then join

sunny_talwar — Thu, 30 Jun 2016 12:54:02 GMT

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

Re: Split a field then join

kevbrown — Thu, 30 Jun 2016 12:57:22 GMT

%Join_terms

Private-Company1-Jan-2015

Private-Company1-Feb-2016

Private-Company2-Mar-2016

Public-NHS1-Feb-2016

Public-NHS2-Apr-2015

Re: Split a field then join

Kushal_Chawda — Thu, 30 Jun 2016 13:01:31 GMT

change it to 2 instead 3..it will work

Re: Split a field then join

Kushal_Chawda — Thu, 30 Jun 2016 13:02:18 GMT

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

Re: Split a field then join

kevbrown — Thu, 30 Jun 2016 13:05:05 GMT

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

Re: Split a field then join

Kushal_Chawda — Thu, 30 Jun 2016 13:19:42 GMT

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

Re: Split a field then join

kevbrown — Thu, 30 Jun 2016 13:22:32 GMT

perfect, thank you

Re: Split a field then join

Kushal_Chawda — Thu, 30 Jun 2016 13:23:54 GMT

welcome