Lookup on same column

[#1]

Hi,

I have a following sample data:

STATUS	Name	PROP_NO
50	JIC	100
90	JIC	120
50	JIC	150
90	JIC	154
50	JIC	162
90	JIC	183
50	JIC	194
50	JIC	101
O	CIC	100
C	CIC	100
O	CIC	120
C	CIC	120
C	CIC	138
C	CIC	143
O	CIC	162

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.

Thanks in advance.

[sunny_talwar]

Try this:

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

[lironbaram]

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

[jonathandienst · Accepted solution]

I go with this:

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

(gives 2 - 100 and 162)

[#5]

thanks a lot everyone.. it worked.

thanks

[#6]

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?

[sunny_talwar]

May be this:

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

[jonathandienst]

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

[#9]

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

Thanks.

[sunny_talwar]

If you got the solution you are looking for, please mark the correct answer and any helpful answers.

Best,

Sunny