how to highlight the data I need in qlik sense

Question

I have address data. The address data is separated by commas and I need to display the city corresponding to this address in the next column on the sheet. The city itself is indicated in the address. Tell me how to select this city from the address, provided that in some addresses it is indicated at the beginning, in some at the end. thanks a lot

Answer 1

There's a ton of variability when it comes to addresses, how you'd split it out depends on the country(s), whether any of them are military, P.O. boxes, etc. Also obviously depends on how your data is formatted.

If your address data has some amount of standardization, like consistent comma separation of address parts, you may just need to use something like =SubField([Address], ',', 2), given an address like 1234 Fake Street, Chicago, IL 60601, where the SubString() function can split your string into one of its parts based on a delimiter, a comma in this case. You can use that function in both the Data Load script and in chart expressions.

There are many formats that won't work that cleanly, though:

Address	=SubField(Address, ',', 2)	Correctly Parsed?
1234 Fake Street, Chicago, IL 60601	Chicago	Yes
1234 Garbage ST., Nonsense, VT, USA	Nonsense	Yes
12 1ST ST NW, HAMPTON, IA 50441-1902	HAMPTON	Yes
1010 CLEAR ST, OTTAWA ON K1A 0B1, CANADA	OTTAWA ON K1A 0B1	No
4321 MAPLE ST, OAKTON MD 12345-6789	OAKTON MD 12345-6789	No

You can see several examples of address formats you may encounter on the USPS site here.

For more advanced parsing, you'll have to contend with the fact that Qlik doesn't have a Regex-like function that could otherwise prove to be useful in this case (unless you happen to have access to Qlik Web Connectors - see more here). You may be able to get clever with the Data Load script by using the SubField() function without the third parameter, like =SubField([Address], ','), using a comma or space as a delimiter (depends on your data) and then using some conditional logic, WHERE clauses, and aggregations to check for city-specific formatting from there.