Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Count with set analysis

Hello,

I´ve 2 tables like:

TABLE_1

PK,

CODIGO,

NR_ANO,

NR_MES

TABLE_2

PK,

NR_ANO_INICIO_COMPETENCIA,

NR_MES_INICIO_COMPETENCIA

I need count CODIGO while NR_ANO = NR_ANO_INICIO_COMPETENCIA.

I´m using this expression:

=Count({$<NR_ANO = NR_ANO_INICIO_COMPETENCIA>} DISTINCT CODIGO)   but isn´t  work.

1 Solution

Accepted Solutions
MVP
MVP

Count with set analysis

Well, I still think that

=Count({$<NR_ANO = {"=NR_ANO=NR_ANO_INICIO_COMPETENCIA"} >} DISTINCT CODIGO)

should work. It's hard to help you if you don't give some more information...

If it's about performance, a distinct count on large data volume is probably always performing not very well.

You could try some of the methods e.g. described in the manual to improve performance on distinct counts.

For example, you could create a new table

LOAD DISTINCT

CODIGO, 1 as CODIGO_DIST

resident TABLE_1;

then do a sum( .... CODIGO_DIST) instead of the count( distinct .... CODIGO)

6 Replies
MVP
MVP

Count with set analysis

This will only work if you select on NR_ANO_INICIO_COMPETENCIA.

Try this:

=Count({$<NR_ANO = p(NR_ANO_INICIO_COMPETENCIA) >} DISTINCT CODIGO)  

Or do you need a record based comparison on these two fields?

MVP
MVP

Count with set analysis

If you do need a record based comparision, try

=Count({$<NR_ANO = {"=NR_ANO=NR_ANO_INICIO_COMPETENCIA"} >} DISTINCT CODIGO) 

Not applicable

Count with set analysis

Hi Swuehl,

I tried to use this expression like that you, but doesn´t work to.

I need count CODIGO when NR_ANO = NR_ANO_INICIO_COMPETENCIA

Thak you

MVP
MVP

Count with set analysis

Have you tried my second expression? If you say, it doesn't work, do you get no results, zero or something, but obviously wrong? Any details on what you are getting back and what do you expect might help.

You could also try an expression without a set expression:

=count(distinct  if(NR_ANO = NR_ANO_INICIO_COMPETENCIA, CODIGO))

BTW, are you using QV10 or some older version?

Regards,

Stefan

Not applicable

Count with set analysis

Hello!

This works fine, but for small amount of records...

Well, thank for your help, and if there is another ideia, please, send me.

Joao

MVP
MVP

Count with set analysis

Well, I still think that

=Count({$<NR_ANO = {"=NR_ANO=NR_ANO_INICIO_COMPETENCIA"} >} DISTINCT CODIGO)

should work. It's hard to help you if you don't give some more information...

If it's about performance, a distinct count on large data volume is probably always performing not very well.

You could try some of the methods e.g. described in the manual to improve performance on distinct counts.

For example, you could create a new table

LOAD DISTINCT

CODIGO, 1 as CODIGO_DIST

resident TABLE_1;

then do a sum( .... CODIGO_DIST) instead of the count( distinct .... CODIGO)

Community Browser