Extracting by Delimiter > Splitting to Columns

[win_anthony]

The goal is to split a column by varying delimiters. All of the items to split have different character lengths. How would you go about this in the script editor? Please see below (Current Example Column vs. Expected Output)

Current Example Column	Expected Output
Location	City	State	Country	Address	Location_Code
San Francisco, CA, USA - One ABC Street (9501)	San Francisco	CA	USA	One ABC Street	9501
Saint-Laurent, QC, CAN - 999 QlikSense Street (MC41)	Saint-Laurent	QC	CAN	999 QlikSense Street	MC41
Columbia, SC, USA - 120 QlikView Drive (B800)	Columbia	SC	USA	120 QlikView Drive	B800
Work at Home - Texas, USA (All Zones) (WTXA)	Work at Home	Texas	USA	All Zones	WTXA
Work at Home - New York, USA (Zone 4) (WNY4)	Work at Home	New York	USA	Zone 4	WNY4

My current syntax is as follows but it is resulting with outputs that are not expected and I cannot figure out (specifically "Work From Home"):

SubField(Location, ',',1) AS City
,SubField(Location, ',',2) AS State
,SubField(SubField(Location, ',',3),'-',1) AS Country
,Subfield(SubField(SubField(Location, ',',3)&SubField(Location, ',',4),'-',2) ,'(',1) AS Address
,Purgechar(Subfield(SubField(SubField(Location, ',',3)&SubField(Location, ',',4),'-',2),'(',2),'()') AS Location_Code 

Below is the output that I am currently getting:

Current Example Column	Current Output (Incorrect)
Location	City	State	Country	Address	Location_Code
San Francisco, CA, USA - One ABC Street (9501)	San Francisco	CA	USA	One ABC Street	9501
Saint-Laurent, QC, CAN - 999 QlikSense Street (MC41)	Saint-Laurent	QC	CAN	999 QlikSense Street	MC41
Columbia, SC, USA - 120 QlikView Drive (B800)	Columbia	SC	USA	120 QlikView Drive	B800
Work at Home - Texas, USA (All Zones) (WTXA)	Work at Home - Texas	USA (All Zones) (WTXA)
Work at Home - New York, USA (Zone 4) (WNY4)	Work at Home - New York	USA (Zone 4) (WNY4)

[Arthur_Fong · Accepted solution]

Try this:

LOCATION:
lOAD * INLINE [
Location
San Francisco, CA, USA - One ABC Street (9501)
Saint-Laurent, QC, CAN - 999 QlikSense Street (MC41)
Columbia, SC, USA - 120 QlikView Drive (B800)
Work at Home - Texas, USA (All Zones) (WTXA)
Work at Home - New York, USA (Zone 4) (WNY4)
](delimiter is '	');

NOCONCATENATE
LOCATION1:
LOAD 
SUBFIELD(Location,',',1)as City,
trim(SUBFIELD(Location,',',2))as State,
subfield(subfield(trim(SUBFIELD(Location,',',3)),3),' ',1)as Country,
subfield(subfield(Location,' - ',2),'(',1) as Address,
mid(subfield(Location,' - ',2),index(subfield(Location,' - ',2),'(')) as Location_Code,
 Location

Resident LOCATION
where not wildmatch(Location,'*Work at Home*');


LOCATION2:
LOAD 
SUBFIELD(Location,'-',1)as City,
trim(subfield(SUBFIELD(Location,'-',2),',',1))as State,
trim(subfield(subfield(SUBFIELD(Location,'-',2),',',2),'(',1))as Country,
subfield(subfield(Location,'(',2),')',1) as Address,
subfield(subfield(Location,'(',3),')',1) as Location_Code,
 Location

Resident LOCATION
where wildmatch(Location,'*Work at Home*');


drop table LOCATION;

[win_anthony]

Thank you so much! The script worked well for most of the output but I am getting some weird duplicates. Mainly resulting in the Country. If we get rid of the weird output from the countries, it will get rid of the duplicates. Any ideas on how I can resolve? Please see screenshot below.

[Arthur_Fong · Accepted solution]

Try this:

LOCATION:
lOAD * INLINE [
Location
San Francisco, CA, USA - One ABC Street (9501)
Saint-Laurent, QC, CAN - 999 QlikSense Street (MC41)
Columbia, SC, USA - 120 QlikView Drive (B800)
Work at Home - Texas, USA (All Zones) (WTXA)
Work at Home - New York, USA (Zone 4) (WNY4)
Atlanta, GA, USA - 1564 Northeast Expressway (S327)
Cary, NC, USA - 11800 Weston Parkway (8673)
Columbus, OH, USA - 2 Miranova Place (7193)
](delimiter is '	');

NOCONCATENATE
LOCATION1:
LOAD 
SUBFIELD(Location,',',1)as City,
trim(SUBFIELD(Location,',',2))as State,
subfield(trim(SUBFIELD(Location,',',3)),' ',1)as Country,
subfield(subfield(Location,' - ',2),'(',1) as Address,
mid(subfield(Location,' - ',2),index(subfield(Location,' - ',2),'(')) as Location_Code,
 Location

Resident LOCATION
where not wildmatch(Location,'*Work at Home*');


LOCATION2:
LOAD 
SUBFIELD(Location,'-',1)as City,
trim(subfield(SUBFIELD(Location,'-',2),',',1))as State,
trim(subfield(subfield(SUBFIELD(Location,'-',2),',',2),'(',1))as Country,
subfield(subfield(Location,'(',2),')',1) as Address,
subfield(subfield(Location,'(',3),')',1) as Location_Code,
 Location

Resident LOCATION
where wildmatch(Location,'*Work at Home*');


drop table LOCATION;

[win_anthony]

Absolutely stunning! So much gratitude!

Cheers and thank you!