If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Comparing Numbers

Started by Zimbo_African_1980, 27 Nov 2018 11:20:36 PM

Previous topic - Next topic

Zimbo_African_1980

This is really a shot in the dark, my imagination may just be wild.
I am working on a data validation report that conditionally formats a list report based on mismatches. I am comparing address between 2 different Data sources to identify those needing update.

for example:
Add_1                          Add_2
8515 CEDAR PLACE   500 E 96TH ST
My conditional formatting will highlight Add_2 in green because of the mismatch in this case. This all seems to work just fine for the most part.

My issue is when there maybe different naming convention.

for example:
Add_1                          Add_2
500 East 96TH ST   500 E 96TH ST

I want to ignore cases like this as you can see its just East instead of E.
My question is, is it possible to compare just the leading #s between 2 variables?
Please keep in mind that addresses have more or less numbers on the Street add e.g 50009 E 96TH ST.

Again, I want to compare just the leading numbers between the 2 address fields.
Thanks for taking your time for this,

R.

Lynn

Quote from: Zimbo_African_1980 on 27 Nov 2018 11:20:36 PM
This is really a shot in the dark, my imagination may just be wild.
I am working on a data validation report that conditionally formats a list report based on mismatches. I am comparing address between 2 different Data sources to identify those needing update.

for example:
Add_1                          Add_2
8515 CEDAR PLACE   500 E 96TH ST
My conditional formatting will highlight Add_2 in green because of the mismatch in this case. This all seems to work just fine for the most part.

My issue is when there maybe different naming convention.

for example:
Add_1                          Add_2
500 East 96TH ST   500 E 96TH ST

I want to ignore cases like this as you can see its just East instead of E.
My question is, is it possible to compare just the leading #s between 2 variables?
Please keep in mind that addresses have more or less numbers on the Street add e.g 50009 E 96TH ST.

Again, I want to compare just the leading numbers between the 2 address fields.
Thanks for taking your time for this,

R.


You can use a stubstring function combined with a  position function to parse out the bit up to the first blank space. This doesn't do anything to look for numbers (versus characters) but might suit your purpose.

substring ( [address 1], 1, position ( ' ', [address 1] ) )

Zimbo_African_1980

Thanks Lynn, this actually worked. I was able to validate and correct about 30 % worth of records.

Lynn

Quote from: Zimbo_African_1980 on 03 Dec 2018 11:03:24 PM
Thanks Lynn, this actually worked. I was able to validate and correct about 30 % worth of records.

Great! Glad I could help.