Help needed with evaluating an expression

[#1]

Hi All,

I want to key in an expression which would return the count of distinct attributes from one column based on the values of another column. Please find the following table and further illustration of this requirement :

Task ID	Transition In	Task	Transition Out
10557115	Proof	R&D	Build
10557115	Proof	R&D	Build
10557115	Proof	R&D	Build
10557115	Proof	R&D	Build
10557115	Proof	R&D	Build
10557115	Hold	R&D	Build
10557115	Hold	R&D	Build
10557115	Hold	R&D	Build
10557115	Hold	R&D	Build
10557115	Hold	R&D	Build
10653829	Review	R&D	Build
11562281	Review	R&D	Build
11562281	Review	R&D	EB
11562281	Review	R&D	EB
12594118	Review	R&D	EB
15423802	Proof	R&D	Build
15423802	Proof	R&D	Build
15423802	Proof	R&D	EB
15423802	Review	R&D	EB
15423802	Review	R&D	EB
15423802	Review	R&D	EB
15423802	Review	R&D	Build

From the above table i want to calculate the count(number of times) a distinct task is in one transition state. For example: I want to calculate the number of times 10557115 task is having Proof in "Transition in" column. (It would be really helpful if you could further expand the expression to include values from multiple column. I want to use this expression in a Table object. Any help is much appreciated.

Thanks & Regards,

Abhinay.

[Gysbert_Wassenaar]

See attached example

[kaushiknsolanki · Accepted solution]

Hi,

     have a look at the attachment.

Regards,

Kaushik Solanki

[nirav_bhimani]

Hi,

You want to count the distinct ID with respect to trasition In then you have to use aggr function for that.

Try this.

Aggr(Count(Distinct TaskID),Transition In)

Regards,

Nirav Bhimani

[#5]

Hi All,

Thanks for your timely reply. Just a small addition to my above request : What needs to be done at the report level itself if i want to have an extra column "Count" which increments itself when a task visits the same transition again, please find the following Table :

Task ID	Transition In	Task	Transition Out	Count
10557115	Proof	R&D	Build	1
10557115	Proof	R&D	Build	2
10557115	Proof	R&D	Build	3
10557115	Proof	R&D	Build	4
10557115	Proof	R&D	Build	5
10557115	Hold	R&D	Build	1
10557115	Hold	R&D	Build	2
10557115	Hold	R&D	Build	3
10557115	Hold	R&D	Build	4
10557115	Hold	R&D	Build	5
10653829	Review	R&D	Build	1
11562281	Review	R&D	Build	1
11562281	Review	R&D	EB	2
11562281	Review	R&D	EB	3
12594118	Review	R&D	EB	1
15423802	Proof	R&D	Build	1
15423802	Proof	R&D	Build	2
15423802	Proof	R&D	EB	3
15423802	Review	R&D	EB	1
15423802	Review	R&D	EB	2
15423802	Review	R&D	EB	3
15423802	Review	R&D	Build	4

should this be achieved at script level/report level, if so can you guys help me with the expression.

I think we should write and iteration statement if we have to handle this at script level.

Sorry, should have been more clear with my communication.

Thanks & Regards,

Abhinay.

[#6]

Hi All,

Thanks for your timely reply. Just a small addition to my above request : What needs to be done at the report level itself if i want to have an extra column "Count" which increments itself when a task visits the same transition again, please find the following Table :

Task ID	Transition In	Task	Transition Out	Count
10557115	Proof	R&D	Build	1
10557115	Proof	R&D	Build	2
10557115	Proof	R&D	Build	3
10557115	Proof	R&D	Build	4
10557115	Proof	R&D	Build	5
10557115	Hold	R&D	Build	1
10557115	Hold	R&D	Build	2
10557115	Hold	R&D	Build	3
10557115	Hold	R&D	Build	4
10557115	Hold	R&D	Build	5
10653829	Review	R&D	Build	1
11562281	Review	R&D	Build	1
11562281	Review	R&D	EB	2
11562281	Review	R&D	EB	3
12594118	Review	R&D	EB	1
15423802	Proof	R&D	Build	1
15423802	Proof	R&D	Build	2
15423802	Proof	R&D	EB	3
15423802	Review	R&D	EB	1
15423802	Review	R&D	EB	2
15423802	Review	R&D	EB	3
15423802	Review	R&D	Build	4

should this be achieved at script level/report level, if so can you guys help me with the expression.

I think we should write and iteration statement if we have to handle this at script level.

Sorry, should have been more clear with my communication.

Thanks & Regards,

Abhinay.

[#7]

Hi Gysbert,

Thanks for the reply. Could you please repost the expression you have given in my other discussion : http://community.qlik.com/thread/80218?tstart=0 .....i think you may have deleted it by mistake....your help in this regard is much appreciated.

Thanks,

Abhinay.