Problem with aggregate/set analysis

[#1]

Hi,

I'm trying to find a way to create a table where I can only see the customers with the same adress code and with demand=0.

So, I have a table like this one:

code	customer	demand
IN0089	1	0
IN0089	5	0
IN0089	8	1
IN0092	9	2
IN0093	10	5
IN0095	11	3
IN0095	13	7

And want to get one like this, for all customers with repeated adress code and demand=0:

code	customer	demand
IN0089	1	0
IN0089	5	0

How can I do it? Should i use a pivot table? (I just can't manage it to work correctly...)

[martin59]

Hello,

Look at the attachement and screenshots to understand.

Hope that helps you.

[#3]

Hello,

Thank you, but it doesn't seem to work (I only qview personal edition so I have some problems opening other users files).

The problem is, and I might not have explained it well enough, when I have customers where the code isn't repeated, so those will appear anyway.

code	customer	demand
IN0089	1	0
IN0089	2	0
IN0089	3	2
IN0090	4	3
IN0090	5	4
IN0090	6	5
IN0091	7	0
IN0092	8	0
IN0093	9	1
IN0094	10	2
IN0094	11	2
IN0095	12	0
IN0095	13	1

To get to:

code	customer	demand
IN0089	1	0
IN0089	2	0
IN0095	12	0

So: I only want to get customers with demand zero whose code appears more than one time (example, customer 7 should not appear).

[martin59 · Accepted solution]

So you can try this :

[jedgson]

You could also create a new field (count) in the script to count the occurances of code

You could then use something like

if(count >1 and demand = 0, 1, 0)

[#6]

Thank you Martin! That's it.