Grouping by Merchant Identity - Rolling up to a Base Name 1

[Jcfx]

Using CR XI against a SQL Database.

This is a summarize grouping question, one that folks dealing with Merchants and Credit Card purchases may have already addressed. Basically I have an expense report where I need to show the top 10 merchants, but… they want the merchants ‘rolled up’ into their base. So if we purchased something from office max, regardless of what the official merchant name is, it all has to be grouped and summed somehow as ‘office max’

Here is some example data:
Merchant Amount

APPLEBEE'S DEN00153130 1.00
APPLEBEE'S DEP96206156 1.00
APPLEBEE'S DUB17000100 1.00
APPLEBEE'S DUN00153213 1.00
APPLEBEE'S EAG19500099 1.00
APPLEBEES-BEND 1.00
APPLEBEES-KLAMATH FALL 1.00
ARBY'S 1.00
ARBYS Q52 1.00
ARBYS JACKSONVILLEQ52 1.00
ARBYS MILLARD Q82 1.00
LA QUINTA INN #9000 1.00
LA QUINTA INN & SUITES 1.00
LA QUINTA MOTOR INNS 1.00
# 0170 LA QUINTA INNS 1.00
# 0453 LA QUINTA INNS 1.00
# 0516 LA QUINTA INNS 1.00
# 0545 LA QUINTA INNS 1.00
MCDONALDS F4757 Q17 1.00
MCDONALD'S F512 Q17 1.00
RPS JAX LIBRARY GARQ02 1.00
RPS LUBBOCK Q02 1.00
RPS PARKING 26-511 Q02 1.00
7-11 #57135 TY 1.00
7-11#57218 1.00
7-ELEVEN 11127 Q39 1.00

<yes I know Seven Eleven are a headache In their own>

I need the out put to look like:
APPLEBEE’s
7
ARBY’S
4
LA QUINTA
7
MCDONALD’s
2
RPS JAX LIBRARY
1
RPS LUBBOCK
1
7-11
3

I know someone else has to have run into this headache before ?
Stripping out punctuation makes life a bit easier for the ones with and without apostrophe’s or spaces.

Uppercase(Replace(replace(Replace(Replace(replace(replace(replace(replace(replace(replace(replace({Merchant.MerchName},".",""),",",""),"-","")," ",""),"#",""),"*",""),"/",""),"&",""),"'",""),"+",""),"@",""))

Now the Merchant field is a maximum of 30 characters, so no matter how bad the naming conventions, I only have to deal with 30 characters.

What would be the best approach to get the grouping?


Julie
CRXI CE10 / RS2005 Sql DB

 

[synapsevampire]

The approach should be done on the database, and I'd set up a parent table with a parent merchant ID, with the other field as the merchant field in your database.

So copy the table to another name, add in a field for parent ID, sort by the child merchant table, then start setting the parent IDs.

Now you can join this to your table and group by the parent ID.

Trying to manage all of the ever changing/growing permutations in a client such as Crystal will be extremely time consuming and won't allow other processes to benefit from the efforts.

Within Crystal you'd need a HUGE IF or CASE statement, such as:

if {table.field} startswith "APPLEBEE'S" then
"APPLEBEE'S"
else
if {table.field} startswith "ARBY'S"
or
{table.field} startswith "ARBYS"
then
"ARBYS"
else
...you get the idea...

Then you could group on that formula field.


Not pretty, and doesn't resolve long term, and if you ever want to use anyting other than Crystal, you'd start over, so I urge you to address it on the database.

-k

 

[Jcfx]

Bah disregard this post, I meant to place in the the SQL Forum.

synampsevampire had already helped me on the crystal side a year ago in this thread:

thread767-1143819

Now it's my turn to figure out how to clean the bloody mess up on the db side





Julie
CRXI CE10 / RS2005 Sql DB

 

[Jcfx]

And.... Synampsevampire has me out posted again.

Thanks again for your suggestions in both threads



Julie
CRXI CE10 / RS2005 Sql DB

 

[synapsevampire]

You can tyr to use SQL to make them similar, but this really is a manual process to get it right.

I use to write reams of code in Cobol, Pascal, xbase, VB, C and sql to address this, but it always ultimately required a manual clean up to get it right.

The process is broken, so address the rpocess.

-k