http://forums.visokio.com/discussions/tagged/postal_ZIP_codes/feed.rss Mon, 30 Oct 17 17:07:48 -0400 Tagged with postal_ZIP_codes - Visokio Forums en-CA http://forums.visokio.com/discussion/2844/mapping-help-need-dummies-guide-to-mapping Thu, 12 Feb 2015 07:35:24 -0500 lcooper 2844@/discussions http://forums.visokio.com/discussion/1813/import-numeric-textzip-codesids-with-leading-zeros Thu, 04 Oct 2012 10:22:10 -0400 sara_llamasoft 1813@/discussions
I'm attaching a sample Excel file containing some US store locations and the associated IOK. This file contains eastern US locations and therefore some of the zip codes have leading zero, e.g. 01752. It's not appropriate to trim the leading zero(s). First, people just expect that the postal code will retain 5 digits. Without the leading zero, it just looks "funny". More importantly, the first three digits have meaning - they're geographical groupings and therefore also have meaning on their own. All 038's are in New Hampshire, etc. We sometimes build models based on the 3-digit zip, so we'd calculate this incorrectly if we lose the leading zeroes. In the IOK, you'll see my solution, which is to use the TEXTVALUE function to enforce 5 digits. Note that it doesn't matter the type in the Excel file (see the two zip code fields). Omniscope handles them identically whether Excel sees them as text or numbers.

It creates considerable additional work if I fall back on the source file input block and simply not type any fields. My issues would be solved for the zip code fields but then I'd have to re-type what would be many more fields back to their intended types.

It would be very useful to selectively type fields or have another way to say on a field-by-field basis, "I know this looks like a number, but treat it like text and don't trim or otherwise modify it".

This would also apply to order numbers or shipment numbers in other circumstances, which would also typically have a fixed-character format, so there's application beyond US postal codes.

Thanks for your consideration! - Sara
]]> http://forums.visokio.com/discussion/1364/extracting-text-using-javascript Wed, 07 Mar 2012 06:12:18 -0500 Guy_Cuthbert 1364@/discussions here, and I thought further examples might be useful... so here is a tutorial on how to extract selected text from a field using regular expressions...

Firstly, the use case to answer the question "Why would I want to do this?":

Omniscope users familiar with the formula language may well have come across the SEARCH and SEARCHREGEX functions, which return the position at which a particular character/phrase (in the case of SEARCH) or regular expression pattern (in the case of SEARCHREGEX). These functions are often used to extract elements of text - for example, extracting parts of a postcode; the UK postcode comprises 4 sections:



So, from a full postcode, we might want to extract the postcode area (first 1-2 characters) or the postcode district (all characters before the space). Finding the space, and extracting everything left of the space, is fairly easy using the SEARCH function, so I will focus on the trickier example of extracting the postcode area.

Using SEARCHREGEX, I can do this in Omniscope by detecting the first alpha character (i.e. A-Z) and the first numeric character (i.e. 0-9) and then extract the characters between these two extremes. To do this we would use:

DECLARE(
/* Store position of first alpha (A-Z) and first nuymeric (0-9) */
firstAZ, SEARCHREGEX("[A-Z]",[Postcode]),
first09, SEARCHREGEX("[0-9]",[Postcode]),
IF(
/* Check that we found both an alpha and a numeric, and that the numeric is after the alpha i.e. a valid postcode format */
AND(firstAZ > 0,first09 > firstAZ),
/* Extract the alpha characters, if valid */
MID([ Postcode], firstAZ, first09-firstAZ),
/* Return nothing if postcode invalid */
NULL
)
)

However, regex extraction is simpler and (once we're confident in regex - lots of tutorials online) allows for far more complex extractions. So here is the same function using Javascript:

SCRIPT(`
// Declare the pattern we want to extract, and then apply it to our input text
var re = /([A-Z]*)/;
var m = re.exec(text1);
// We should have one (or more) groups of character sets now
if (m == null) {
// If not, then return "No"
'No'
} else {
// If we do have at least one character group, return the first (could return many, concatenated, if required)
m[0]
}
`, "text1", [Postcode])

The structure of this is:

1. Firstly we have the Javascript code itself, which includes the use of a variable called "text1"
2. Then, in the last line, we have the declaration of "text1" (this can be called anything, and needs to be the same name here as inside the script itself, and passing the value of our [Postcode] field into this variable, for use in the script

This only scrapes the surface of this (Javascript regex) approach - there are many ways to use this as the basis for far more complex, and valuable, text extractions... but as Steve's original article demonstrates, there are many, many more uses for Javascript.

Happy scripting... ]]>