Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bruce_sorge
Contributor III
Contributor III

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
jonathandienst
Partner - Champion III
Partner - Champion III

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

View solution in original post

5 Replies
dwforest
Specialist II
Specialist II

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

bruce_sorge
Contributor III
Contributor III
Author

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.

jonathandienst
Partner - Champion III
Partner - Champion III

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
jonathandienst
Partner - Champion III
Partner - Champion III

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
Contributor III
Contributor III
Author

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.