topic Re: players_list in QlikView

players_list

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: players_list

ciaran_mcgowan — Thu, 07 Jul 2016 10:21:36 GMT

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

List of all 630 players + the 420 batting players
Find matching players in both lists and only show these matching ones
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.

Re: players_list

nareshthavidishetty — Thu, 07 Jul 2016 10:42:26 GMT

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

Re: players_list

varshavig12 — Thu, 07 Jul 2016 11:00:22 GMT

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

Re: players_list

ciaran_mcgowan — Thu, 07 Jul 2016 11:08:56 GMT

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?

Re: players_list

ciaran_mcgowan — Thu, 07 Jul 2016 11:18:05 GMT

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.

Re: players_list

nareshthavidishetty — Thu, 07 Jul 2016 12:17:46 GMT

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.

Re: players_list

ciaran_mcgowan — Thu, 07 Jul 2016 12:36:43 GMT

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!

Re: players_list

varshavig12 — Fri, 08 Jul 2016 03:27:23 GMT

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.