Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Zip = Left(Mid(LOCATION, Index(LOCATION,'Zip: ')+6),5)
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.
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?
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;
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.