Count new enteries

[#1]

Hi

I have a table with 2 columns. Column one is the name of the ceremonies and the second one is for participants of that ceremony.

Now i want to have the number of new persons in each ceremoney.

this is my table:

Person_Code     Ceremony

50                       a

        50                    b

        50                    c

        51                    b

        52                        b

so the result should be like this:

ceremoney    number_New_Participants

a                         1      =(50)

b                         2     =(51,52) (not 50 because it is in a ceremony)

c                         0      (not 50 because it is in a ceremony)

can you please help me making the formula.

thank you very much in advanced.

[swuehl]

Yes, quite a clever solution.

You are using the fact that implicite DISTINCT qualifier on aggr will return only one value (and you and me assume that this will be the first Person_Code in load order), right?

If the Ceremony order is determined by load order, this is probably the shortest expression I could think of, good job!

[IAMDV]

Stefan - That's true. I am really impressed by qliksus. It is just simple and I love the fact that sometimes we need to go back and do reverse engineering. Also, your solution is good. However, please can you explain your expression in simple english? I can't get my head around on your idea.

Thanks again. Also, I have subscribed to your posts. So you might see me asking more questions...

Cheers - DV