topic Help with possibly looping within an expression in QlikView

Help with possibly looping within an expression

Wed, 01 Dec 2010 11:39:38 GMT


COUNT(DISTINCT( If (IsNull(CLOSURE_CODE_ID)=0 AND OWNINGLOCATION='1ST' AND SUM(IF(LOCATION='1ST', 0, ENQUIRY))=0, ENQUIRY)))

In the above expression I need to count all records that have a value for OwningLocation as "1ST" and also that every record in a related table for the same Enquiry has a value for Location as "1ST" also.

The second table can concievably have any number of records relating to the one Enquiry. So i figured doing a sum of all those records that dont equal "1ST" would be one way to loop round them all.

For Example The tables (simplified) could look like this:

Table A

ENQUIRY	OWNINGLOCATION
121	1ST
122	1ST
123	SER
124	2ND
125	1ST

Table B

ENQUIRY	LOCATION	Sequence No
121	1ST	0
121	1ST	1
121	1ST	2
121	1ST	3
122	1ST	0
122	2ND	1
123	SER	0
124	1ST	0
124	2ND	1
124	2ND	2
125	1ST	0
125	1ST	1

So From Table A all i want to count is 121 and 125 as these are the only two enquiries that have "1ST" as the location in every record on the tables. All the other enquiries have moved to a different location at some point.

I am stumped.
And I am sure everyone reading this is also. Clear as mud ?!

Help with possibly looping within an expression

pover — Wed, 01 Dec 2010 23:07:47 GMT

You could possibly do this with set analysis in the following way:

count({$<OWNINGLOCATION={'1ST'},ENQUIRY=E({1<LOCATION-={'1ST'}>})>} distinct ENQUIRY)

which will exclude any enquiry that has a value in location different than '1ST'.

Regards.

Help with possibly looping within an expression

syed_muzammil — Wed, 01 Dec 2010 23:37:31 GMT

Hi,

May be this would work

count({$<OWNINGLOCATION={'1ST'},ENQUIRY={"=count({$<LOCATION={'1ST'}>} ENQUIRY)=Count(ENQUIRY)"}>} distinct ENQUIRY)

But Karl's Solution is better. 😄

Regards,

Syed.

Help with possibly looping within an expression

Thu, 02 Dec 2010 10:40:16 GMT

Thanks guys.

Karl, yours worked perfectly.
I just need to figure out now what it does!