Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bruce_sorge
New Contributor II

Extracting Data from One Excel Field

Greetings and apologies if this has been asked, but I searched the questions and could not find an answer.

I have an Excel Spreadsheet I am currently using to create a point map. One cell I am interested in is the address cell. Currently, data is stored like this:

Name: The Ohio State University
Address: 1800 Cannon Dr Rm 1210
City: Columbus
State: OH
Zip: 43210
County: Franklin

This spreadsheet is created via a website we have. What I need is just the zip code. I have connected the data in Qlik Sense and I think that I need to create an expression to extract the zip for the map. However, I am relatively new to Qlik and thus am not sure how to do this. I am attaching the spreadsheet with the information.  The information in the spreadsheet is publicly available information.

As an aside, I am attending the conference next month so hopefully I will glean tons of information from the workshops.

1 Solution

Accepted Solutions
MVP
MVP

Re: Extracting Data from One Excel Field

This will work, but because there can be more than one ZIP code for a row on the source, you will get a few duplicated rows. If that is not OK, then you will need to decide which ZIP to take (first, last, max, min or a concatenated list).

Data:
LOAD NUMBER, 
	[EPA ID], 
	ABSTRACT, 
	[View Project Details], 
	INSTITUTION, 
	[GRANT AMOUNT], 
	[PROJECT PERIOD], 
	STATE, 
	LOCATION,
	RFA
FROM [OpenGrants20190423.xlsx]
(ooxml, embedded labels, table is [searchresults-2]);


NVPairs:
LOAD *
Where Len(Value) > 0;
LOAD NUMBER,
	SubField(LocFields, ': ', 1) as Name,
	Text(SubField(LocFields, ': ', 2)) as Value;
LOAD NUMBER,
	SubField(LOCATION, chr(10)) as LocFields
Resident Data;

Join (Data)
LOAD NUMBER, 
	Value as ZIPCode
Resident NVPairs
WHERE Name = 'Zip';

Or

Join (Data)
LOAD NUMBER,
Concat(Value, ',') as ZIPCode
Resident NVPairs
WHERE Name = 'Zip'
Group By NUMBER;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
5 Replies
Highlighted
dwforest
Valued Contributor

Re: Extracting Data from One Excel Field

Zip = Left(Mid(LOCATION, Index(LOCATION,'Zip: ')+6),5)

bruce_sorge
New Contributor II

Re: Extracting Data from One Excel Field

Hi and thanks for the reply. Apologies if I seem dense, but I am not sure how to insert this. Currently the expression says =Location. If I put this in stead of =Location, I get Zip is not a valid function. If I add it after location, I get garbage after Zip.

MVP
MVP

Re: Extracting Data from One Excel Field

The spreadsheet is very awkward as a data source. For example, for some rows, there are multiple instances of the address/zip groups. What other data are you extracting? How is that associated with the zip codes?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Extracting Data from One Excel Field

This will work, but because there can be more than one ZIP code for a row on the source, you will get a few duplicated rows. If that is not OK, then you will need to decide which ZIP to take (first, last, max, min or a concatenated list).

Data:
LOAD NUMBER, 
	[EPA ID], 
	ABSTRACT, 
	[View Project Details], 
	INSTITUTION, 
	[GRANT AMOUNT], 
	[PROJECT PERIOD], 
	STATE, 
	LOCATION,
	RFA
FROM [OpenGrants20190423.xlsx]
(ooxml, embedded labels, table is [searchresults-2]);


NVPairs:
LOAD *
Where Len(Value) > 0;
LOAD NUMBER,
	SubField(LocFields, ': ', 1) as Name,
	Text(SubField(LocFields, ': ', 2)) as Value;
LOAD NUMBER,
	SubField(LOCATION, chr(10)) as LocFields
Resident Data;

Join (Data)
LOAD NUMBER, 
	Value as ZIPCode
Resident NVPairs
WHERE Name = 'Zip';

Or

Join (Data)
LOAD NUMBER,
Concat(Value, ',') as ZIPCode
Resident NVPairs
WHERE Name = 'Zip'
Group By NUMBER;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
bruce_sorge
New Contributor II

Re: Extracting Data from One Excel Field

Good morning, 

This is a good question. The data is coming from an Oracle database of grants that we have funded over the decades. Since it's a relational database, the programmer did their best to maintain those relationships when downloading the search results into a spreadsheet. Since more than one institution can receive an award, those institutions will show up in the same cell. It looks like garbage data for sure, but my end goal is to tie my application into the actual Oracle database. I just have to get this particular chart online soon with just plots of institutions that have active grants across the country.