Count of IDs- Use of set analysis

sguerra2 — Sat, 16 Nov 2024 17:46:18 GMT

Hi team,

Hope you are doing great! I need your help! I am trying to perform a count with a set analysis... let me explain the situation:

I have two bases. The count i have to do is the amount of product code (from BASE 1) but considering the characteristics of BASE 2- (for example Juan is in ARG and the sector is GREEN) the amount that should result is : 9 because shouldnt consider CHILE and other SECTORS. However what I noticed is that if the Nurse number that Juan has in the BASE 2 is present in BASE 1 related to another sector (nurse 456), the product code is summarized (painted in red in the screen).... I need to exclude that one, so the numbers in the count are just for juan's sector in BASE 2.

	BASE 1

COUNTRY	PROD CODE	PRODUCT	DOCTOR	SECTOR 1	NURSE NUMBER
ARG	1	APPLE	JUAN	GREEN	123
ARG	2	APPLE	JUAN	GREEN	456
ARG	3	APPLE	LUIS	GREEN	789
ARG	4	APPLE	LUIS	GREEN	1122
ARG	5	APPLE	CARLOS	GREEN	1455
ARG	6	APPLE	CARLOS	GREEN	1788
ARG	7	APPLE	MARCO	GREEN	2121
ARG	8	APPLE	MARCO	GREEN	2454
ARG	9	APPLE	MARTA	GREEN	2787
ARG	10	APPLE	MARTA	ORANGE	3120
ARG	11	APPLE	OSCAR	ORANGE	3453
CHI	12	LEMON	JUAN	GREEN	456
ARG	12	ORANGE	JUAN	RED	456
ARG	13	BANANA		YELLOW

BASE 2

COUNTRY	DOCTOR	SECTOR 2	nurse number
ARG	JUAN	GREEN	123
ARG	JUAN	GREEN	456
ARG	LUIS	GREEN	789
ARG	LUIS	GREEN	1122
ARG	CARLOS	GREEN	1455
ARG	CARLOS	GREEN	1788
ARG	MARCO	GREEN	2121
ARG	MARCO	GREEN	2454
ARG	MARTA	GREEN	2787
ARG	MARTA	GREEN	3120
ARG	OSCAR	GREEN	3453

I used the expresion: COUNT({<sector 1=p(sector 1),PRODUCT=p(PRODUCT),COUNTRY=p(COUNTRY)>}prod code)

Really appreciate your help!

Regards.

Sofía

topic Count of IDs- Use of set analysis in App Development

Count of IDs- Use of set analysis