Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
As it given in the excel sheet and the last column Partnership, i need the sum of scores if the pairs of striker and non striker are same.
example Gayle - Uthappa is 27 and Uthappa - Gayle is 24, so the partnership is 2 +24 = 51.
Please help how to solve in qlik sense. Thanks in advance!!
This would do it :
script:
map:
Mapping LOAD Distinct
NonStriker&' - '&Striker as Pairs,
Score
FROM [lib://DOSSIER telechargements/BatVsBall.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
partners:
load *,
ApplyMap('map',Pairs) as Score2;
LOAD
MatchId,
Striker,
NonStriker,
Striker&' - '&NonStriker as Pairs,
Score,
PartnerShip
FROM [lib://DOSSIER telechargements/BatVsBall.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
in ur table: dimension: Pairs
measure: sum(Score)+sum(Score2)
and u can filter by pairs, striker or nonStriker
Sorry 27 + 24 = 51
And how exactly u want to show this partnership?
I mean:
I'd propose sthing like this:
etc..
To do that:
1) in the script:
map:
Mapping LOAD
NonStriker as Striker,
Score
FROM [lib://DOSSIER telechargements/BatVsBall.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
partners:
LOAD
MatchId,
Striker,
NonStriker,
ApplyMap('map',Striker) as Score2,
Score,
PartnerShip
FROM [lib://DOSSIER telechargements/BatVsBall.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
2) in the sheet :
as filter : Stiker:
in your table:
Dimension :
=Striker& ' - '&NonStriker name it partnerShip
Measure:
sum(Score+Score2) name it Score
then under adds-on:
COUNt(distinct Striker)=1
Result:
Hope this helps !
Omar BEN SALEM.
That's not that accurate; I'll be retaking a look at it after the launch !
Hi,
You can use the script below to get your desired output.
Script :
Master:
load *,
IF(ISNULL(Lookup('fullname',fullname_alt, fullname)),fullname,Lookup('fullname',fullname_alt, fullname)) as Pair;
LOAD
MatchId,
Striker,
NonStriker,
Striker&'-'&NonStriker as fullname,
NonStriker&'-'&Striker as fullname_alt,
Score
FROM [lib://Desktop/BatVsBall.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
drop Fields fullname,fullname_alt from Master;
In Pivot Table;
Dimensions : Pair,Striker
Measure : Sum(Score)
This would do it :
script:
map:
Mapping LOAD Distinct
NonStriker&' - '&Striker as Pairs,
Score
FROM [lib://DOSSIER telechargements/BatVsBall.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
partners:
load *,
ApplyMap('map',Pairs) as Score2;
LOAD
MatchId,
Striker,
NonStriker,
Striker&' - '&NonStriker as Pairs,
Score,
PartnerShip
FROM [lib://DOSSIER telechargements/BatVsBall.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
in ur table: dimension: Pairs
measure: sum(Score)+sum(Score2)
and u can filter by pairs, striker or nonStriker
Hey omarbensalem...Thanks a lot for the quick response!! This is what the exact solution I looked for.
Oh... one more question... which I was forgot(unnoticed) to mention in the previous query.. Is there any way to exclude the duplicate mutual pairs from the table list! either one Gayle-Kohli or Kohli-gayle is required, not both!!
u should try to apply kaanerisen's method !It should work !