topic Re: Count new enteries in QlikView

Count new enteries

Sat, 15 Oct 2011 07:18:34 GMT

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.

Count new enteries

SunilChauhan — Sat, 15 Oct 2011 14:39:11 GMT

how you identify that perticular person is in ceremony or not

Re: Count new enteries

swuehl — Sat, 15 Oct 2011 20:09:39 GMT

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

Sun, 16 Oct 2011 10:51:13 GMT

Thank you for your response,

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

Re: Count new enteries

Sun, 16 Oct 2011 10:53:09 GMT

Thank you for your response,

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

swuehl — Sun, 16 Oct 2011 17:40:56 GMT

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

Sun, 16 Oct 2011 17:41:07 GMT

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

Re: Count new enteries

Mon, 17 Oct 2011 00:54:55 GMT

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

Count new enteries

qliksus — Mon, 17 Oct 2011 02:56:55 GMT

Hi,

try this in expression

count(aggr(Person_Code,Person_Code))

Re: Count new enteries

IAMDV — Mon, 17 Oct 2011 13:51:35 GMT

qliksus - Very clever workaround.

Cheers - DV

Count new enteries

swuehl — Mon, 17 Oct 2011 14:12:21 GMT

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

IAMDV — Mon, 17 Oct 2011 14:34:33 GMT

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