topic Re: Help needed with evaluating an expression in QlikView

Help needed with evaluating an expression

Thu, 18 Apr 2013 04:48:26 GMT

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.

Re: Help needed with evaluating an expression

Gysbert_Wassenaar — Thu, 18 Apr 2013 06:34:28 GMT

See attached example

Re: Help needed with evaluating an expression

kaushiknsolanki — Thu, 18 Apr 2013 06:45:23 GMT

Hi,

have a look at the attachment.

Regards,

Kaushik Solanki

Re: Help needed with evaluating an expression

nirav_bhimani — Thu, 18 Apr 2013 09:42:35 GMT

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

Re: Help needed with evaluating an expression

Thu, 18 Apr 2013 14:26:18 GMT

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.

Re: Help needed with evaluating an expression

Fri, 19 Apr 2013 04:27:25 GMT

Hi All,

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.

Re: Help needed with evaluating an expression

Sun, 21 Apr 2013 09:42:23 GMT

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.