topic Re: Visits using the PatientID count - Load Script in App Development

Visits using the PatientID count - Load Script

sharasridhar — Thu, 13 Feb 2020 20:57:42 GMT

I need to add a new table using load script.

I have a master table with patientIDs, and their visit summary (name, purpose, diagnoses, location,charge etc).

The PatientIDs repeat throughout the table whenever the patient revisits.

I need a new table which consolidates all the unique patientIDs in one column and the second column should have the count of each patientID (which would be the number of times he visited).

For example if the below table is the data set:

ID	Name
ID001	abc
ID001	abc
ID001	abc
ID002	yhn
ID002	yhn
ID003	ujn
ID004	koi
ID004	koi

I want the result table which looks like below:

ID	Visits
ID001	3
ID002	2
ID003	1
ID004	2

Re: Visits using the PatientID count - Load Script

Anonymous — Thu, 13 Feb 2020 21:09:17 GMT

This should work:

[Visit_Table]:
LOAD
[ID],
[Name]
FROM ORIGINAL_TABLE;

[Visit_Count_Table]:
Noconcatenate
LOAD
[ID],
Count([Name]) as [Visit Count]
RESIDENT [Visit_Table]

GROUP BY [ID];

Re: Visits using the PatientID count - Load Script

mdmukramali — Thu, 13 Feb 2020 21:25:00 GMT

Hi,

Patients:
Load * Inline
[
ID,Name
ID001,abc
ID001,abc
ID001,abc
ID002,yhn
ID002,yhn
ID003,ujn
ID004,koi
ID004,koi
]
;Load ID,
Count(Name) as TotalVisits
Resident Patients
Group By ID
;

Re: Visits using the PatientID count - Load Script

sharasridhar — Thu, 13 Feb 2020 21:32:12 GMT

Thank you, it worked without the concatenate.

Re: Visits using the PatientID count - Load Script

sharasridhar — Thu, 13 Feb 2020 21:32:32 GMT

Perfect Thanks!!