Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You're requirement isn't very clear, do you want to
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.
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
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
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?
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.
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.
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!
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.