Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Not applicable

## Lookup on same column

Hi,

I have a following sample data:

STATUSNamePROP_NO
50JIC100
90JIC120
50JIC150
90JIC154
50JIC162
90JIC183
50JIC194
50JIC101
OCIC100
CCIC100
OCIC120
CCIC120
CCIC138
CCIC143
OCIC162

I want to perform a calculation where i need a count of those PROP_NO which have STATUS=50 in JIC and are present in CIC.

For example: in this sample data, the count should be 2 (PROP_NO 100 and 162 has STATUS=50 in JIC and are present in CIC)

Kindly help me with the logic to be applied in set analysis.

1 Solution

Accepted Solutions
Partner - Champion III

I go with this:

=Count({<STATUS = {'50'}, Name = {'JIC'}, PROP_NO = P({<Name = {'CIC'}>} PROP_NO)>} Distinct PROP_NO)

(gives 2 - 100 and 162)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
9 Replies
MVP

Try this:

=Count(DISTINCT {<PROP_NO = {"=Count({<STATUS = {50}, Name = {'JIC'}>}PROP_NO) > 0"}*{"=Count({<Name = {'CIC'}>}PROP_NO) > 0"}>}PROP_NO)

Partner - Master III

hi

this expression does the trick

=sum(aggr(if(count({<STATUS={'50'},  Name={'JIC'}>}PROP_NO)>0 and count({<Name={'CIC'}>}PROP_NO)>0,1,0),PROP_NO))

i am attaching a model also

Partner - Champion III

I go with this:

=Count({<STATUS = {'50'}, Name = {'JIC'}, PROP_NO = P({<Name = {'CIC'}>} PROP_NO)>} Distinct PROP_NO)

(gives 2 - 100 and 162)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

thanks a lot everyone.. it worked.

thanks

Not applicable
Author

one more thing..

what if i want those records that have STATUS=50 in JIC and are not present in CIC.

how wud that work?

MVP

May be this:

=Count(DISTINCT {<PROP_NO = {"=Count({<STATUS = {50}, Name = {'JIC'}>}PROP_NO) > 0"}-{"=Count({<Name = {'CIC'}>}PROP_NO) > 0"}>}PROP_NO)

Partner - Champion III

=Count({<STATUS = {'50'}, Name = {'JIC'}, PROP_NO = E({<Name = {'CIC'}>} PROP_NO)>} Distinct PROP_NO)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

@JonathanDienst Thanku. I tried your mentioned solution earlier. It was not working before but now its fine.

Thanks.

MVP