topic Re: "special" count in QlikView

"special" count

Thu, 24 Apr 2014 08:59:19 GMT

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!

Re: "special" count

Thu, 24 Apr 2014 09:08:08 GMT

Try to concat fields in the same sort order.

For example

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

Re: "special" count

bertdijks — Thu, 24 Apr 2014 09:09:32 GMT

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
;

Re: "special" count

Thu, 24 Apr 2014 09:31:00 GMT

Thank you,

It's work!

Re: "special" count

Thu, 24 Apr 2014 09:36:54 GMT

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

Re: "special" count

MarcoWedel — Fri, 25 Apr 2014 00:41:16 GMT

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

Re: "special" count

Fri, 25 Apr 2014 07:53:23 GMT

Thank you, Marco!

Very interesting and original solution!