Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sharasridhar
Contributor III
Contributor III

Visits using the PatientID count - Load Script

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:

IDName

ID001

abc
ID001abc
ID001abc
ID002yhn
ID002yhn
ID003ujn
ID004koi
ID004koi

 

I want the result table which looks like below:

IDVisits
ID0013
ID0022
ID0031
ID0042

 

2 Solutions

Accepted Solutions
mdurso2626
Contributor II
Contributor II

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

View solution in original post

mdmukramali
Specialist III
Specialist III

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
;

 

 
 

 

 

View solution in original post

4 Replies
mdurso2626
Contributor II
Contributor II

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
Specialist III
Specialist III

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
Contributor III
Contributor III
Author

Thank you, it worked without the concatenate.

sharasridhar
Contributor III
Contributor III
Author

Perfect Thanks!!