"special" count

[#1]

Hi,

I have a table T1:

Field1, Field2

a,b

c,d

b,a

a,b

When I try to get а count:

load
Field1, Field2, count(*)

...

the result is

a,b,2

c,d,1

b,a,1

but for my logic the rows "a,b" and "b,a" are equivalent, so the desired result should be as:

a,b,3

c,d,1

can anyone advise the best way to solve this problem?

Thanks!

[#2 · Accepted solution]

Try to concat fields in the same sort order.

For example

if(Field1>Field2, Field1&'-'&Field2, Field2&'-'&Field1)

[bertdijks]

Maybe something like this?

LOAD * INLINE [
    Field1, Field2
    a, b
    c, d
    b, a
    a, b
];



T2:
Load
Field1 as Field1b,
Field2 as Field2b,
count(distinct 1)
resident T1
group by Field1, Field2
;

[#4]

Thank you,

It's work!

[#5]

Thank you, for reply!

in your case the result is:

a,b,1

c,d,1

b,a,1

but I need

a,b,3

c,d,1

[MarcoWedel]

Hi Oleg,

another possibility could be:

tabInput:
LOAD
  RecNo() as RecNo,
  AutoNumberHash128(Field1, Field2) as %ID,
  *
Inline [
Field1, Field2
a,b
c,d
b,a
a,b
];

tabFieldCombinations:
CrossTable(FieldName, FieldValue)
LOAD Distinct
  %ID,
  Field1,
  Field2
Resident tabInput;

Left Join (tabInput)
LOAD
  %ID,
  Concat(FieldValue, ',') as FieldCombination
Resident tabFieldCombinations
Group By %ID;

hope this helps also

regards

Marco

[#7]

Thank you, Marco!

Very interesting and original solution!