Visits using the PatientID count - Load Script

[sharasridhar]

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

[Anonymous · Accepted solution]

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];

[mdmukramali · Accepted solution]

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
;

[sharasridhar]

Thank you, it worked without the concatenate.

[sharasridhar]

Perfect Thanks!!