load script to count

[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

[Lisa_P · Accepted solution]

After you have loaded the first table, using that as a data source in a script like this:

Table2:
Load ID,
        count(name) as Visits
resident Table1
Group by ID;

[DataShark]

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?

[Almen]

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.

[DataShark]

Okay hmm, I wonder then what causes my script to result in "Field not found" error, my script is as follows:

LOAD
FileId,
Count(RowId) as COUNT_RowId;
SELECT
FileId,
RowId
FROM "YYY".XXX.GLData
Group by FileId;

And returns:

The following error occurred:

Field 'RowId' not found

[Lisa_P]

Try the Group by in the Load statement..

LOAD
FileId,
Count(RowId) as COUNT_RowId
Group by FileId;
SELECT
FileId,
RowId
FROM "YYY".XXX.GLData;

[alkoni]

Hi Sharasridhar,

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:

DataSet:
LOAD
[ID],
[Name]
FROM [lib://AttachedFiles/DataSet.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Result_Table:
Load
ID,
count(ID) as NoOfVisit
Resident DataSet
group by ID;

Inner join(Result_Table)
Load
ID
Resident Result_Table;

drop Table DataSet;

exit Script;

Hope you are clear, Thanks