Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jino_kannen
New Contributor

Compare different fields of rows and sum up the values

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

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Compare different fields of rows and sum up the values

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

Capture.PNG

11 Replies
jino_kannen
New Contributor

Re: Compare different fields of rows and sum up the values

Sorry 27 + 24 = 51

OmarBenSalem
Esteemed Contributor

Re: Compare different fields of rows and sum up the values

And how exactly u want to show this partnership?

I mean:

Capture.PNG

OmarBenSalem
Esteemed Contributor

Re: Compare different fields of rows and sum up the values

I'd propose sthing like this:

Capture.PNG

Capture.PNG

Capture.PNG

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 Capture.PNG

Result:

Capture.PNG

Hope this helps !

Omar BEN SALEM.

OmarBenSalem
Esteemed Contributor

Re: Compare different fields of rows and sum up the values

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

kaanerisen
Contributor III

Re: Compare different fields of rows and sum up the values

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)

Untitled.png

OmarBenSalem
Esteemed Contributor

Re: Compare different fields of rows and sum up the values

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

Capture.PNG

jino_kannen
New Contributor

Re: Compare different fields of rows and sum up the values

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

jino_kannen
New Contributor

Re: Compare different fields of rows and sum up the values

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
Esteemed Contributor

Re: Compare different fields of rows and sum up the values

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

Community Browser