String count based on similar names

[montubhardwaj]

Hello all,

I have a particular requirement where I need to count sales  persons in different states. If for a particular product, same sales person is available in multiple states, count should be 1 for that product. If there are multiple sales persons for a product in multiple states, count should differ accordingly. Below is the example I have given. If you have any easy way to implement, please let me know.

Prod A	Dave Alaska	Total count   for Prod A=1
Prod A	Dave Chicago
Prod A	Dave Florida
Prod B	John NY	Total Count for Prod B=3
Prod B	John NJ
Prod B	Merry Texas
Prod B	Lisa LA

[#2]

PFA.

[montubhardwaj]

Hello RR, thanks for your time.

The above is just an example I gave. You can not separate states from their names. Moreover there are like 1000,s of records in the database. I am looking for a process/method where I can compare the names under any product and if the names are similar (lets say first 10 chars), the will count as 1 else count will change.

[MarcoWedel]

Hi Dinesh,

if there is any seperator between sales person and state in this combined "sales person-state" field, then you could do like:

tabSales:
LOAD
  @1 as Product,
  SubField(@2, ' ', 1) as SalesPerson,
  SubField(@2, ' ', 2) as State
FROM [http://community.qlik.com/thread/114322]
(html, codepage is 1252, no labels, table is @1);

If there is no seperator and the lengths of the sales persons names is not fixed, then there is no algorithm to solve this problem and you should probably work on your data quality instead.

regards

Marco

[montubhardwaj]

yes you are right. Lengths of sales persons names are not fixed so I have to look at this problem from data quality standpoint. Thanks for your time though.

[arjunkrishnan]

Hello Frd ,

I try This Script

LOAD PRODUCT,

     NAME,

     AutoNumber(SubField(NAME,' ',1))as COUNT_NUM

FROM

Excel.xls

(biff, embedded labels, table is [Sheet1$]);

My Out Put Like That is it Ok For You..

Regard By ArjunKrish