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:
I want the result table which looks like below:
I have a massive dataset and would like to know how many records are in which field.
I can't get the count working straight from SQL and all answers seem to expect the use of Resident table.
Do I have to load ALL data into a resident table first to be able to use counts?
You don't have to use a resident table, but you have to group your data set if you use any type of aggregation like count or sum.
With SQL the same would apply, use group by so you can count whatever field you like. I think you can also use COL_LENGTH so group by is not necessary.
Okay hmm, I wonder then what causes my script to result in "Field not found" error, my script is as follows:
Count(RowId) as COUNT_RowId;
Group by FileId;
Please refer the attached xlsx file and then first you have to load the main table say for example, DataSet (.xlsx file) and then create a temp table as Result_Table from Resident of DataSet table then Inner Join By ID with the table created from Resident of Result_Table which was newly created table and then finally drop the DataSet table as per the below script:
(ooxml, embedded labels, table is Sheet1);
count(ID) as NoOfVisit
group by ID;
drop Table DataSet;
Hope you are clear, Thanks