517095_en-US

liliconcom

Re: RegEx experts...help extracting QTY from opportunity strings

Hi,

 

The attached works for the examples provided.  If it doesn't work for future examples I can modify as needed.

 

Re: RegEx experts...help extracting QTY from opportunity strings

Thanks, Hadley. That didn't scale super well. Should have just posted the full data set: https://jmplivesales.na.sas.com:3501/packages/5c1d45033684d60d9c48d109-1

From the worldwide dashboard: "Worldwide won deals report"

 

Re: RegEx experts...help extracting QTY from opportunity strings

The regex for the upgrades will depend on what you expect to see in the parentheses and in what order. Do you have more example of how the opportunity field may appear?

Re: RegEx experts...help extracting QTY from opportunity strings

The full table is here:

RegEx experts...help extracting QTY from opportunity strings

I'm working with the Salesforce data and looking to extra some data (as much as I can) from opportunity names.

Attached is a sample data set. From a given opportunity, I'd like to extract the Product and Qty if it is a NAL opportunity (we have the type column to help with that), for a Subscription it's always 1, and for upgrades many of the opportunities (but not all) have a from to to part of the string, which should be able to determine that From - To == QTY. Upgrades often have product in there too.

I also want to remove non-english characters from the name as part of the regex.

Thanks.

Here is the full table.

 

Re: RegEx experts...help extracting QTY from opportunity strings

Don -- I added a OneDrive link for the full table.

Re: RegEx experts...help extracting QTY from opportunity strings

Looking at the table from Hadley's link, it would be hard to come up with anything that would work with all the observations. The input is too unstructured. How close were you hoping to get?

Re: RegEx experts...help extracting QTY from opportunity strings

I was hoping to get a reasonable sample. It doesn't have to be every transaction. That sample can be extrapolated to the full quarter pretty reasonably. I do have another data set that I have been working with, so if this isn't going to work out, I can figure something else out.

Re: RegEx experts...help extracting QTY from opportunity strings

This gets about 85% of observations. I've used three regex statements that will be most likely license numbers (as opposed to dates, contract numbers, IDs, etc.) The last statement, p4, may inadvertently get something not a count (a digit surrounded by white space). You could generalize this a bit more, but I'm afraid you'd wind up with non license count values.

Re: RegEx experts...help extracting QTY from opportunity strings

Nice, Don! Let me play around with these data a bit.

Re: RegEx experts...help extracting QTY from opportunity strings

Here is the JMP Live report. Had to do some additional Recoding with "Pro" -- any company name with Pro in it was erroneously being coded as a Pro buy. 

https://jmplivesales.na.sas.com:3501/packages/xKszCJ3dKYhSNhgtGZKJH

I think there is still some opportunities to improve the data processing work. I need to put this into a workflow too.

Re: RegEx experts...help extracting QTY from opportunity strings

I agree. If you can think of a pattern that is most likely corresponds to number of licenses, I can come up with the regex.

Re: RegEx experts...help extracting QTY from opportunity strings

Thanks Don. This is impressive work so far, and I don't think I said that yet. I appreciate both you and Hadley looking at the data set.

It also speaks to the power of data cleanup in JMP and the ability to put structure on the largely unstructured.

What was unusable is now usable.

It's more than a representative sample of the types of NALs and Upgrades we get, too. I did end up removing Gov't and Academic territories. In the future having clean data understanding exactly what type of customer class the contract is, which product, what they purchased and how it was pro-rated (n/12s) of the year is the desired end-state.

The new pricing tiers also will help with being able to audit transactions much more easily.

Re: RegEx experts...help extracting QTY from opportunity strings

Thanks Dan,

What's really impressive is the phenomenal job that Craige Hailes did to implement the regex functionality (at least, I think it was Craige). I am floored that both positive and negative look ahead AND look behind are both available and work spectacularly.