CODEWITHSUNDEEP
frequencyspotfire
V. Ang
V. Ang

Reputation: 65

Spotfire: calculate the frequency of value pairs in a column

I have the following table in Spotfire:

ID  Rule  
1   a  
1   b  
1   a  
1   c  
2   a  
2   a  
3   a  
3   a  
3   b  
4   b  
4   c  
5   c

I need to calculate how many times each rule appears with each other rule. The result from this table should be: 

Couple  Occurrences  
a-b     2            (Explanation: from ID 1 and ID 3)  
a-c     1            (Explanation: from ID 1)  
b-c     2            (Explanation: from ID 1 and ID 4)

or something like this:

   a   b   c
a  3   2   1
b  2   3   2
c  1   2   3

(and I will ignore a-a, b-b and c-c).

I don't know how to obtain this in Spotfire without adding too many columns (there are 21 millions of rows and 60 different rules, so I don't want to increase too much the dimensions of this file).

Do you know what can I do? I imagine I will have to use a script but I don't know how to build it.

Thank you!

Valentina

Upvotes: 2

Views: 766

Answers (1)

V. Ang
V. Ang

Reputation: 65

I've solved my problem using 

Rank = DenseRank([row],[data],[ID])

("data" is an additional variable I have in my data)

Concatenate = UniqueConcatenate([Rule]) OVER (Intersect(AllNext([rank]),[ID],[data]))

and then taking the combinations where [rank] = 1. It's not a perfect solution because I don't obtain value pairs but I obtain every combination of rules I can find in my data.

I am still interested in a better solution, but maybe someone could find this answer useful.

Thank you,

Valentina

Upvotes: 1

Related Questions