To add another field so I can join

[#1]

Hi folks,

I am trying to add a ProductName filed in Table1. Then I will join Table1 with Table2 on ProductName.

In Table1, I have a ProductDescription column. The first several letters of each ProductDescription indicates the ProductName.  Eg. The ProductName of “Pandacookielover” is “Panda”. The ProductName of “Tomatorocks” is “Tomato”.

Could you guys give me some idea how I can handle this issue please?

Thanks a lot!!

Jaylynn

In table1

I have a “ProductDescription” field as follows.

Table1

ProductDescripton	Colum1	Colum2	Colum3	Colum4	Colum5
Pandacookielover
Pandacleaningmodel
Tigerneverlaugh
Tigerlaughtoomuch
Tomatorocks
Tomatoclubmonster
Onionflyhighhigh
Oninmissedpotato
Handbagcansing
Handbagoutdated
Toyknowsfashion
Cosmeticbeauty
Costmeticicecream
Toydreamsoftea
Lettuceguy
Orangejuicy
Shoeshatesocks
Shoesspeakchinese
Jacketdancer
Musechocolate
Lionking
Lionrabbit
Potatogame
Orangejacket
Onionmarrieslettuce
Shoestoetips
Cosmeticdancer
Musemomkitty
Toystory
Pandababy
Tigerrocket

Table2

ProductFamilyName	Code	ProductName
Zoo	Zoosection1	Tiger
Zoo	Zoosection2	Panda
Zoo	Zoosection4	Tigerfish
Zoo	Zoosection3	Muse
Zoo	Zoosection1	Lion
GroceryMkt	GroceryMktsection1	Tomato
GroceryMkt	GroceryMktsection1	Potato
GroceryMkt	GroceryMktsection2	Orange
GroceryMkt	GroceryMktsection1	Lettuce
GroceryMkt	GroceryMktsection1	Onion
DeptStore	DeptStoreSection1	Jacket
DeptStore	DeptStoreSection2	Shoes
DeptStore	DeptStoreSection2	Handbag
DeptStore	DeptStoreSection3	Toy
DeptStore	DeptStoreSection4	Cosmetic

[cspencer3]

It seems like to do this you would need some form of a standard format such as the first 5 letters, or have a hyphen, comma, or some indicator to be able to separate Tiger from TigerRocket for example. That or start using a product ID field to your data.

[#3]

Thanks, Charles. The problem is that I have 700+ ProductNames and 10,000+ ProductDescriptions. The lenths of the ProductNames is from 2-10...

I am really not sure how I can pick the ProductNames from the ProductDescriptions...:(

[NickHoff]

This is possible creating fuzzy match logic.  You'll have to locate the distance between the two words in a vbscript algorithm as well.  Take a look at the following example:

Fuzzy Matching/Joining

[tobias_klett]

Hi FastCat,
do you really have product names which are part of other product names?, such as tiger and tigerfish?

How much does the length of product Name vary? Are they all between 4 and 9 letters?

Tobias

[#6]

Hi Tobias,

Yes. I do have product names are part of other product names such as Tiger and Tigerfish. But in my case it is Tiger and Fishtiger. It's the later part of the other product name.

The length of product names vary from 2-10.

Thanks!!

FastCat

[tobias_klett]

Hi FastCat,

ok, if the ProductName is never the beginning of another PorductName my idea is the following:

1. Read the ProductNames into a mapping table like this:

Map_ProductNames:

Mapping Load distinct

     ProductName as Inputfield,

     ProductName as ResultField

Resident Table2;

2. Create your new field like this:

Table1:

Load ....,

     applymap('Map_ProductNames',left(ProductDescription,2),'') & applymap(...,3),'') & ... 4 ... 10),'') as Product,

...

Hope this helps

Tobias 

[#8]

Hi Tobias,

Thanks! It looks durable. I forgot to tell that some of the ProductDescriptions might not be able to find any correspondent ProductNames. I plan to name them "Other". How do I realize it?

Thanks a lot.

FastCat

[#9]

Hi Tobias,

I tried it in your way and it's almost working!!

I did find some "TigerFish" which means there are some Product Names are first part of other Product Names. I also found some Ti-ger for example should be included into the "Tiger" group...

Please advise if you have any idea!!

Thanks,

FastCat

[tobias_klett]

Hi TigerCat, 🙂

so we have three things left:

1. Label the ProductDescriptions without ProductNames with "Others".

if( len( OurExpression = 0, 'Others', OurExpression) as ProductName

2. Characters as "-" in "Ti-ger" should be removed:

purgechar( 'T$i-g%er', '$-%') for example results in "Tiger". So just purgechar the ProductDescriptions.

3. ProductNames beginning with other ProductNames:

Start with 10 (or even longer because of the charcters, see number 2.) and go down to 9,8,...,2. Do this using an if for each step like this:

if( len(applymap('Map_ProductNames',left(ProductDescription,10),''))>0 // Condition

  ,       applymap('Map_ProductNames',left(ProcuctDescription,10),'')     // true -> take

  , if( len .......same with 9,8,7                                  // else -> go down one step

Note: With this solution you can write 'Others' in the last "Else" instead of using number 1.

Hope this helps
Tobias