Compare different fields of rows and sum up the values

[jino_kannen]

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!!

[jino_kannen]

Sorry 27 + 24 = 51

[OmarBenSalem]

And how exactly u want to show this partnership?

I mean:

[OmarBenSalem]

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.

[OmarBenSalem]

That's not that accurate; I'll be retaking a look at it after the launch !

[kaanerisen]

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)

[OmarBenSalem · Accepted solution]

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

[jino_kannen]

Hey omarbensalem‌...Thanks a lot for the quick response!! This is what the exact solution I looked for.

[jino_kannen]

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!!

[OmarBenSalem]

u should try to apply kaanerisen's method !It should work !