Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

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
Highlighted
Contributor II
Contributor II

Re: Visits using the PatientID count - Load Script

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

Highlighted
Specialist III
Specialist III

Re: Visits using the PatientID count - Load Script

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
Highlighted
Contributor II
Contributor II

Re: Visits using the PatientID count - Load Script

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

Highlighted
Specialist III
Specialist III

Re: Visits using the PatientID count - Load Script

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

Highlighted
Contributor II
Contributor II

Re: Visits using the PatientID count - Load Script

Thank you, it worked without the concatenate.

Highlighted
Contributor II
Contributor II

Re: Visits using the PatientID count - Load Script

Perfect Thanks!!