11 Replies Latest reply: Oct 17, 2011 10:34 AM by Deepak Vadithala

# Count new enteries

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)

thank you very much in advanced.

• ###### Count new enteries

how you identify that perticular person is in ceremony or not

?

• ###### Re: Count new enteries

Hi MK_Komaei,

If I understood you correctly, the temporal order of your Ceremonies is determined by alphabetical order of their names.

If so, I think you could use something like

=count( if(aggr(NODISTINCT MinString(aggr(Ceremony,Ceremony,Person_Code)),Person_Code)=Ceremony,Person_Code))

as chart expression.

Alternatively, you could do similar in the script using a group by LOAD.

See attached sample.

Hope this helps,

Stefan

edit: And if you Ceremonies are ordered by something else, you could probably just add a numerical CeremonyOrder field and use this field and  Min instead of MinString in above expression.

• ###### Re: Count new enteries

But in fact it did not help me. as I changed the ceremony from alphabetic into a code as below but the result will be changed incorrectly.

I changed

a=10012

b=102

c=1003

d=100224

• ###### Count new enteries

Hi,

try this in expression

count(aggr(Person_Code,Person_Code))

• ###### Re: Count new enteries

qliksus - Very clever workaround.

Cheers - DV

• ###### Count new enteries

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!

• ###### Re: Count new enteries

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

• ###### Re: Count new enteries

But in fact it did not help me. as I changed the ceremony from alphabetic into a code as below but the result will be changed incorrectly.

I changed

a=10012

b=102

c=1003

d=100224

the file is attached below.

• ###### Re: Count new enteries

Hi MK_Komaei,

you need to somehow define the order of your Ceremonies, otherwise it's hard to say when to count a Person_Code as new entry.

I assume that in your sample, you define this order by load order.

As I said, all you need to do is adding a column with the Ceremonies order, see attached as how I do this for load order.

If you have more information, e.g. a Ceremony date, you could use this date as order (a recognized date has a numerical representation).

Hope this helps,

Stefan

• ###### Re: Count new enteries

Hi there.....check the attached doc....I've changed one small bit in the script.

• ###### Re: Count new enteries

Forgot to mention, the qv doc I used is swuehl's....just changed one line in there..