topic Re: Flagging non distinct numbers in QlikView

Flagging non distinct numbers

Mon, 16 Jul 2012 14:56:47 GMT

Hello all, I know you can count distinct numbers/fields etc, but how do you go about flagging up non Distinct:- here is some data as an example.

Customer Number | Property ref.

001 | 15 <-

002 | 16

003 | 15 <-

004 | 17

Customer 001 and 003 have same property number, I want to 'Flag' these if they occur more than once, can anyone please provide formulae for such example please.

Paul.

Re: Flagging non distinct numbers

Mon, 16 Jul 2012 15:01:53 GMT

try the folloing in the script: if(FiledName = previous(FieldName), 'N','Y') as Distinct Regards R

Re: Flagging non distinct numbers

swuehl — Mon, 16 Jul 2012 15:20:29 GMT

There are probably multiple ways to achieve what you want, load script based and front end only.

For example, you could create a list box with an expression (select <expression> from field list), then enter as expression:

=aggr(if(count([Property ref.])>1,[Customer Number]),[Customer Number])

Or create a list box for field [Customer Number], then add an expression (from expression tab):

if(count([Property ref.])>1,'Multiple','Once')

Re: Flagging non distinct numbers

CELAMBARASAN — Mon, 16 Jul 2012 15:22:13 GMT

Hi,

For do it in load script

Main:

Load

From

DataSource;

Flag:

Load

[Property ref.],

If(Count([Property ref.]) > 1, 1, 0) AS Flag \\1 for non distinct, 0 for distinct

Resident

Main

Group by [Property ref.];

Hope it helps

Re: Flagging non distinct numbers

Tue, 17 Jul 2012 10:53:23 GMT

Hi Swuehl,

if(count([Property ref.])>1,'Multiple','Once') looks promising, but when I go to the list box I cannot search by multiple, is there a way to make this searchable?

Paul.

Re: Flagging non distinct numbers

swuehl — Wed, 18 Jul 2012 17:28:59 GMT

Paul,

I noticed that my suggested expression won't work as provided (using just the customer as list box field).

So either create a flag in the script as suggested above, or do something similar in the front end, if needed:

=aggr(nodistinct if(count( PartNr)>1,'Multiple','Once'),PartNr)

Unfortunately, you can't search the expression values in a list box, but you can use a straight table with dimension Customer and above expression and make the expression searchable. But a straight table has some limitations and differences compared to a list box.

In a list box, you could create a combined Customer / Flag string to show and select from, maybe something like

=aggr(nodistinct num(CustomerNr,'000') &' - '& if(count( total<PartNr> PartNr)>1,'MANY','ONE'), PartNr,CustomerNr)

If you don't need to be selection sensitive, I would probably go for the script solution.

Regards,

Stefan