topic String count based on similar names in QlikView

String count based on similar names

montubhardwaj — Tue, 15 Apr 2014 00:54:24 GMT

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

Re: String count based on similar names

Tue, 15 Apr 2014 01:00:38 GMT

PFA.

Re: String count based on similar names

montubhardwaj — Tue, 15 Apr 2014 01:07:52 GMT

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.

Re: String count based on similar names

MarcoWedel — Tue, 15 Apr 2014 01:29:17 GMT

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

Re: String count based on similar names

montubhardwaj — Tue, 15 Apr 2014 01:51:37 GMT

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.

Re: String count based on similar names

arjunkrishnan — Tue, 15 Apr 2014 05:22:49 GMT

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