topic Query on Multiple Dimentions in App Development

Query on Multiple Dimentions

SivanVigder — Fri, 02 Apr 2021 08:07:16 GMT

Here is a table of Codes that Dentists assigned as treatment they have done to different Teeth in different Appointments. It can be that same Dentist assigns multiple Codes in an Appointment, and also that two Dentists assigned different or similar Codes in the same Appointment.

Code Dentist Appt Tooth

A10 S 1 37

H35 S 1 37

A10 A 2 38

H35 A 2 17

A10 B 3 28

H35 A 3 28

A10 A 4 37

H11 A 4 37

Query 1 needed - Count appointments where Code H35 or H11 assigned in combination with A10 at the same Appt by the same Dentist

Query 1a.

Count appointments where Code H35 or H11 assigned in combination with A10 at the same Appt by same Dentist for the same Tooth

Query 2 - wild card

Count appointments where any H?? Code (any H followed by 2 digits) was assigned in combination with A10 at the same Appt by same Dentist and the same Tooth

Query 2a. - wilder card

Same like query 2 only wild card H* (H followed by anything )

———

Re: Query on Multiple Dimentions

asinha1991 — Thu, 26 Sep 2019 18:40:30 GMT

are you ok with load script solutions? if not let me know what kind of visualization you are creating

Q1:

Load sum(finalcount) as finalcountQ1;

load if( (wildmatch(concat(CODE,',') ,'*H35*') or wildmatch(concat(CODE,',') ,'*H11*')) and wildmatch(concat(CODE,','),'*A10*'),1) as finalcount,DENTIST FROM yourdatasource GROUP BY DENTIST,APPT;

Q1a

Load sum(finalcount) as finalcountQ1a;

load if( (wildmatch(concat(CODE,',') ,'*H35*') or wildmatch(concat(CODE,',') ,'*H11*')) and wildmatch(concat(CODE,','),'*A10*'),1) as finalcount,DENTIST,Tooth FROM yourdatasource GROUP BY DENTIST,APPT,Tooth;

Load sum(finalcount) as finalcountQ2;

load if( wildmatch(concat(CODE,',') ,'*H*') and wildmatch(concat(CODE,','),'*A10*'),1) as finalcount,DENTIST,Tooth FROM yourdatasource GROUP BY DENTIST,APPT,Tooth;

Q2a

how it is different than Q2?

Re: Query on Multiple Dimentions

asinha1991 — Fri, 27 Sep 2019 06:25:01 GMT

I am replying here because my limit for private messages has exceeded (surprisingly)

for frontend you can convert what I sent to aggr maybe, somethings like this

for Q1

load if( (wildmatch(concat(CODE,',') ,'*H35*') or wildmatch(concat(CODE,',') ,'*H11*')) and wildmatch(concat(CODE,','),'*A10*'),1) as finalcount,DENTIST FROM yourdatasource GROUP BY DENTIST,APPT;

change it to sum(aggr( if( (wildmatch(concat(CODE,',') ,'*H35*') or wildmatch(concat(CODE,',') ,'*H11*')) and wildmatch(concat(CODE,','),'*A10*'),1) ,DENTIST,APPT))

just add parameter you group by in aggr

Re: Query on Multiple Dimentions

rishabirshad — Fri, 28 Apr 2023 10:55:43 GMT

@SivanVigder wrote:

Here is a table of Codes that Dentists assigned as treatment they have done to different Teeth in different Appointments. It can be that same Dentist assigns multiple Codes in an Appointment, and also that two Dentists assigned different or similar Codes in the same Appointment.

Code Dentist Appt Tooth

A10 S 1 37

H35 S 1 37

A10 A 2 38

H35 A 2 17

A10 B 3 28

H35 A 3 28

A10 A 4 37

H11 A 4 37

Query 1 needed - Count appointments where Code H35 or H11 assigned in combination with A10 at the same Appt by the same Dentist

Query 1a.

Count appointments where Code H35 or H11 assigned in combination with A10 at the same Appt by same Dentist for the same Tooth

Query 2 - wild card

Count appointments where any H?? Code (any H followed by 2 digits) was assigned in combination with A10 at the same Appt by same Dentist and the same Tooth

Query 2a. - wilder card

Same like query 2 only wild card H* (H followed by anything )

———

Query 1: Count appointments where Code H35 or H11 assigned in combination with A10 at the same Appt by the same Dentist.

To answer this query, we need to find the appointments where both A10 and either H35 or H11 codes were assigned in the same appointment by the same dentist. We can use the following SQL query for this:

SELECT COUNT(DISTINCT Appt)
FROM table_name
WHERE (Code = 'A10' OR Code = 'H35' OR Code = 'H11')
AND Dentist = 'S'
GROUP BY Appt
HAVING COUNT(DISTINCT Code) = 2;

This will give you the count of appointments where both A10 and either H35 or H11 codes were assigned in the same appointment by the same dentist (in this case, 'S').

Query 1a: Count appointments where Code H35 or H11 assigned in combination with A10 at the same Appt by same Dentist for the same Tooth.

To answer this query, we need to find the appointments where both A10 and either H35 or H11 codes were assigned in the same appointment by the same dentist for the same tooth. We can use the following SQL query for this:

SELECT COUNT(DISTINCT Appt)
FROM table_name
WHERE (Code = 'A10' OR Code = 'H35' OR Code = 'H11')
AND Dentist = 'S'
GROUP BY Appt, Tooth
HAVING COUNT(DISTINCT Code) = 2;

This will give you the count of appointments where both A10 and either H35 or H11 codes were assigned in the same appointment by the same dentist for the same tooth.

Query 2: Count appointments where any H?? Code (any H followed by 2 digits) was assigned in combination with A10 at the same Appt by the same Dentist and the same Tooth.

To answer this query, we need to find the appointments where any code starting with H (followed by two digits) and A10 were assigned in the same appointment by the same dentist for the same tooth. We can use the following SQL query for this:

SELECT COUNT(DISTINCT Appt)
FROM table_name
WHERE (Code LIKE 'H__' OR Code = 'A10')
AND Dentist = 'S'
GROUP BY Appt, Tooth
HAVING COUNT(DISTINCT Code) = 2;

This will give you the count of appointments where any code starting with H (followed by two digits) and A10 were assigned in the same appointment by the same dentist for the same tooth.

Query 2a: Same like query 2 only wild card H* (H followed by anything).

To answer this query, we need to find the appointments where any code starting with H and A10 were assigned in the same appointment by the same dentist for the same tooth. We can use the following SQL query for this:

SELECT COUNT(DISTINCT Appt)
FROM table_name
WHERE (Code LIKE 'H%' OR Code = 'A10')
AND Dentist = 'S'
GROUP BY Appt, Tooth
HAVING COUNT(DISTINCT Code) = 2;

This will give you the count of appointments where any code starting with H and A10 were assigned in the same appointment by the same dentist for the same tooth.

Re: Query on Multiple Dimentions

Chanty4u — Fri, 28 Apr 2023 11:15:47 GMT

Query 1

COUNT(

DISTINCT {<Code={'A10'}, Dentist={$(=concat(DISTINCT Dentist, ','))}>} Appt)

Q1a)

COUNT(

DISTINCT {<Code={'A10'}, Dentist={$(=concat(DISTINCT Dentist, ','))}>}

IF(

COUNT(

DISTINCT {<Code={'H35','H11'}, Tooth={$(=max(Tooth))}, Appt={$(=max(Appt))}, Dentist={$(=concat(DISTINCT Dentist, ','))}>} Appt

) > 0,

Appt

)

COUNT(

DISTINCT {<Code={'A10'}, Dentist={$(=concat(DISTINCT Dentist, ','))}>}

IF(

COUNT(

DISTINCT {<Code={'H??'}, Tooth={$(=max(Tooth))}, Appt={$(=max(Appt))}, Dentist={$(=concat(DISTINCT Dentist, ','))}>} Appt

) > 0,

Appt

)

Q2a

COUNT(

DISTINCT {<Code={'A10'}, Dentist={$(=concat(DISTINCT Dentist, ','))}>}

IF(

COUNT(

DISTINCT {<Code={'H*'}, Tooth={$(=max(Tooth))}, Appt={$(=max(Appt))}, Dentist={$(=concat(DISTINCT Dentist, ','))}>} Appt

) > 0,

Appt

)