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

load script to count

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

 

1 Solution

Accepted Solutions
Lisa_P
Employee
Employee

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;

 

 

View solution in original post

6 Replies
Lisa_P
Employee
Employee

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

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
Creator II
Creator II

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

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
Employee
Employee

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

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