Sum of Quantity based on match

[engishfaque]

Dear All,

I have a scenario where I have to merge products and calculate the quantity.

For example: Product name is "Gadsden35", this text can be occurred in first, middle or in last of product name as well as name can be in upper or lower case such as illustrated in listed below table.

Input

Product	Quantity
NI-R6501-Gadsden35-MF-006-Set3	1
ADF-JPS-001	1
NI-gadsden35	3
ADF-JPS-001-ZZ0	1
NI-R6501-Gadsden35	1
ADF-JPs-001-ZZ1	1
NI-R6501-Gadsden35-Set2	2

Now, I have to merge them and calculate the Quantity such as Gadsden35 quantity = 7

Required Output

Product	Quantity
Gadsden35	7
ADF-JPS-001	3

Please note, product names vary in my data source file. So, Gadsden35 is not always remain the same as the selection vary the product names vary.

Kind regards,

Ishfaque Ahmed

[Anonymous]

Hi!

I had a similar problem in one project of mine...

If you know the list of these names you want to show, you can match them in the script.

Load

Product,

if(Capitalize(Product) like '*GADSDEN35*', 'Gadsden35',

  if(Capitalize(Product) like '*ADF-JPS-001*' ,'ADF-JPS-001', ........)) as ProductNew.

In my case, there were few values to be matched... if you have lots of them, maybe it can be difficult

Elena

[engishfaque]

Dear Elena,

I have large set of data.

My data file consist on 387,615 records. It's difficult.

Kind regards,

Ishfaque Ahmed

[MarcoWedel]

Please specify how your Product names can be identified in your product field?

Regards

Marco

[engishfaque]

Dear Marco,

My product names are different from starting to end but if there is any one word match to each other then it must be add.

Such as given above example input and output.

Kind regards,

Ishfaque Ahmed

[robert_mika]

This is probably impossible task as having let say

"ABC"

and "ABC1"

the matching values will be:

A

B

C

AB

ABC...

[engishfaque]

Dear Robert,

Yes It's. Any suggestion or alternative?

Kind regards,

Ishfaque Ahmed

[MarcoWedel]

If you can't define an algorithm, there will be no solution ...

regards

Marco

[robert_mika]

Maybe I'm wrong but if you are selling/buying those products you must have defined some unique identification for them.

Otherwise this business is kind of "unique" one...;)

Can you dig deeper in your database?

Maybe you will find something that would help here.

[engishfaque]

Dear Robert/Marco,

Yes you are right.

I'm trying to find pattern, as I can solve it.

Kind regards,

Ishfaque Ahmed