9 Replies Latest reply: Aug 12, 2015 12:16 AM by Sunny Talwar

# 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.

• ###### Re: Lookup on same column

Try this:

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

• ###### Re: Lookup on same column

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

• ###### Re: Lookup on same column

I go with this:

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

(gives 2 - 100 and 162)

• ###### Re: Lookup on same column

thanks a lot everyone.. it worked.

thanks

• ###### Re: Lookup on same column

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?

• ###### Re: Lookup on same column

May be this:

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

• ###### Re: Lookup on same column

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

• ###### Re: Lookup on same column

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

Thanks.