11 Replies Latest reply: Dec 12, 2017 10:32 PM by Jino Kannen

# 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.

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

Sorry 27 + 24 = 51

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

And how exactly u want to show this partnership?

I mean:

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

I'd propose sthing like this:

etc..

To do that:

1) in the script:

map:

NonStriker as Striker,

Score

FROM [lib://DOSSIER telechargements/BatVsBall.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

partners:

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:

Dimension :

=Striker& ' - '&NonStriker  name it partnerShip

Measure:

sum(Score+Score2) name it Score

COUNt(distinct Striker)=1

Result:

Hope this helps !

Omar BEN SALEM.

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

• ###### 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:

IF(ISNULL(Lookup('fullname',fullname_alt, fullname)),fullname,Lookup('fullname',fullname_alt, fullname)) as Pair;

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)

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

This would do it :

script:

map:

NonStriker&' - '&Striker as Pairs,

Score

FROM [lib://DOSSIER telechargements/BatVsBall.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

partners:

ApplyMap('map',Pairs) as Score2;

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

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

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

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

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

Check my previous post if you don't want to see any duplicate mutual pairs. You should tagged the records as one pair like 'Gayle-Kohli' and 'Kohli-Gayle' records should be tagged as 'Kohli-Gayle' as pair. So there is no way that field has duplicate records.

You can try the script in my previous post to see if it works for you

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

thanks kaanerisen ..it worked well as it required. Thanks both of you!! (@omar)

• ###### 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.