How do I alter a business name to something different?

[#1]

Hello.

The data pulled displayed a listing of business locations titled, "Servicing_Provider_Address" in my script. However, the data displayed in that group listed several physical addresses rather than the actual business names. It will eventually need to be changed from the data source but for now I'd like to alter it in my script if possible.

In the dashboard, within my chart, it displays the following example to name a few:

"Top 25 Sending Providers Business Locations

129 W. Hibiscus Blvd. Suite A

211 New Haven Ave

1515 West Nasa Blvd, Suite 101

Hospice

Hibiscus Office Park"

There are more that need to be corrected however this was a brief example. They need to be altered to display the following:

"Top 25 Sending Providers Business Location

129 W. Hibiscus Blvd. Suite A  (changed to)  East Coast Orthopedics

211 New Haven Ave  (changed to)  Melbourne Podiatry Associates

1515 West Nasa Blvd, Suite 101  (changed to)  Porter Premier Dermatology

Hospice (changed to) VITAS

Hibiscus Office Park  (changed to)  Florida Therapy Center Melbourne"

This is currently what my script displays however I keep getting a reload error:

if(SERVICING_PROVIDER_ADDRESS like 'Hospice*', 'VITAS',
(if(SERVICING_PROVIDER_ADDRESS like 'Atlantic Orthopedic Group*', 'Paul Keller, MD',
(if (SERVICING_PROVIDER_ADDRESS like 'Atlantic Orthopedics*','John Perry, MD', SERVICING_PROVIDER_ADDRESS))))) as SERVICING_PROVIDER_ADDRESS, // bucket overlapping dxs

The above script is an example of several business locations that need to be changed however since I keep getting an error on those three locations/addresses I have stopped adding the rest due to the reload error.

Does anyone know what might be causing the reload error? Or how I can alter the business location names?

Thank you,

[Colin-Albert]

The best solution would be to load the correct field that has the business name rather than trying to fudge it.

If you want to try and fix it, then look at using ApplyMap and a mapping table rather that loads of if statements.

That way you could use a spreadsheet or database table as the data source for the mapping table which would be easier to manage.

Have a look at this post Don't join - use Applymap instead

ApplyMap is one of my favorite functions and is very powerful (and fast!)

[swuehl]

See also

Data Cleansing

[#4]

Colin,

Thank you for the suggestions. I'll proceed with correcting the data field rather than the "fudge" attempt. I appreciate your input.

Thank you,

DB

[#5]

Swuehl,

Thank you for the attachment. It looks very informational that I was definitely look at. I appreciate you sharing this. Thank you again for the feedback.

Take care,

DB

[stevedark]

I would agree with Colin_Albert that ApplyMap is the way to go.  I would also suggest that you have it so you wind up with two fields, one with the original and the other with the cleansed values.  You could also use two different applications of the same ApplyMap, one to give a clean name or the original and another that replaces non-cleansed values with 'Other', like this:

LOAD

  *,

  if([Original Address] = [Clean Address], 'No', 'Yes') as [Address Cleaned]

  ;

LOAD

  SERVICING_PROVIDER_ADDRESS  as [Original Address],

  ApplyMap('Map_CleanAddresses', SERVICING_PROVIDER_ADDRESS) as [Clean Address],

  ApplyMap('Map_CleanAddresses', SERVICING_PROVIDER_ADDRESS, 'Other') as [Clean Address Or Other]

  ...

FROM DataSource.qvd (Qvd)

;

Full details of how to create the Mapping table and how to use it (and why the two applications are different) can be found in this blog post:

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Hope that helps.

Steve

[#7]

Steve,

Great idea. Thank you for sharing the blog post. I'll make sure to review that as well. I appreciate your feedback.

Thank you,

DB