topic Extracting by Delimiter > Splitting to Columns in QlikView

Extracting by Delimiter > Splitting to Columns

win_anthony — Sat, 16 Nov 2024 00:52:01 GMT

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)

Re: Extracting by Delimiter > Splitting to Columns

Arthur_Fong — Tue, 14 Apr 2020 08:28:33 GMT

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;

Re: Extracting by Delimiter > Splitting to Columns

win_anthony — Tue, 14 Apr 2020 13:56:29 GMT

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.

Re: Extracting by Delimiter > Splitting to Columns

Arthur_Fong — Tue, 14 Apr 2020 14:13:17 GMT

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;

Re: Extracting by Delimiter > Splitting to Columns

win_anthony — Tue, 14 Apr 2020 14:27:08 GMT

Absolutely stunning! So much gratitude!

Cheers and thank you!