players_list

[nareshthavidishetty]

Hi everyone i got struck at 1 point

i got 2 tables players qvd   (630 players),other is batting (420 players)

qvd                                    batiing

age,                                    runsscored

players                                 player

teams                                   year

my requirement is to get full player list (630)

qvd players list contains some short name of the players,some long names same as with batting

eg: aron finch in qvd where as A.finch in batting

      Ashish Reddy in qvd where as A.Ashish Reddy in batting table

i need to get full player list in one unique form either short or long Names kindly help me out

[ciaran_mcgowan]

You're requirement isn't very clear, do you want to

1. List of all 630 players + the 420 batting players
2. Find matching players in both lists and only show these matching ones
3. Find matching players in both lists and show both these lists, but with duplication removed (i.e. list all QVD players + remaining Batting players)

The data is not good here, for a start there are not 630 players in your original list, there are things like 'SQUAD', 'TEAM', 'Amazon  The Stands', etc.

[nareshthavidishetty]

thanks for the response jus leave every thing consider two fields players,player

players,          player

s tendulkar      sachin tendulkar

r.dravid            rahul dravid

aron finch          a .finch

michel hayden     m hayden

now my requirement is to get the unique player  list from these two fields

[varshavig12]

You have to try converting all the names in same format.

Something Like

=left(SubField(Name,' ',1),1)&'.'&if(isnull(SubField(Name,' ',3)),SubField(Name,' ',2),SubField(Name,' ',3))

Using above expression: Zaheer Khan will turn into Z.Khan

Note:

You can fall in trouble.

for eg:

If your format is First letter of first name . Last Name

1. Zaheer Khan

2. Zain Khan

Then both will give you Z.Khan

[ciaran_mcgowan]

You are not going to be able to accomplish what you need unless there is a commonality between both field that is unique.

The best you can do with the data provided is to create a PLAYERID using the first letter from the first name and the full surname, but the will still fail. For example, the players Amanpreet Singh, Amit Singh and Amitoze Singh from the QVD will all become ASINGH, so that's not unique. There are also 3 ASINGH's in the Batting list but only one of them exists in the QVD list.

Is there no way you can get your hands on cleaner data?

[ciaran_mcgowan]

Agreed. There are not enough unique values to create an ID that links both columns. As mentioned before, there is 1 A Singh in the Batters list but 5 A Singh's in the QVD.

Qlikview is amazing, but it can't do miracles I'm afraid. You need to get your hands on cleaner data.

[nareshthavidishetty]

so what should i do in order to get the correct names as you specified there are many players who fall with the example you given ashish reddy ,ashith reddy both showing as A.Reddy tell me how to overcome this problem

thanks for the response.

[ciaran_mcgowan]

You cannot. You need to clean the data manually, otherwise you data cannot be joined 100% correctly. There will be lots of issues matching names and there is no way around it, I'm afraid.

If A.Reddy is all you have to go on in the Batting list, it cannot equal both Ashish Reddy and Ashith Reddy from the  QVD......

Unless your name is Schrodinger!

[varshavig12]

I guess there's only 2 ways:

1. Get Clean data. (best thing if possible)

2. Try to find some kind of unique ID mapped with names, which can help you differentiate Z.Khan as  Zaheer or Zain. And then you can convert all in same format.