Create list box or equiv showing just a count of duplicate values

[#1]

Hi Everyone,

I am working on some dashboards where I have to show how many times a 'customer' has been in contact. The data is broken down to surname, first name, DOB. The loading script puts this into a numeric string and then I use this string to report on, plus its useful to hide 'customer' ID'able data.

What I am after in the first instance is how to create a object showing a count of distinct customers whic allows you to filter other objects by the number of contacts being greater then say3.

The data set is approaching 1.5 million rows and the only answer I can come up with so far is list box containing the string as s dimension and a count distinct of the string. This takes many seconds to refresh and is not really workable.

Does anyone have an idea on how I could acheive this?

Many thanks.

[swuehl]

Hi Phil,

you might consider putting the count calculation into the script, like

LOAD customerString,

count (customerString) as cntCustomer

resident yourDataTable group by customerString;

Regards,

Stefan

[#3]

Hi,

I thought about doing that but I have a date selection on the dashboard and it must show the number of times we have a contact with that 'customer' within the date selected. Putting a count in the script would give a count of the entire dataset, wouldn't it?

Phil

[swuehl]

Yes, it would.

Are you really using a string or a number coded from Names / DOB?

You may consider replacing the count distinct (do you really count distinct or all contacts? I probably missed some point in your original post) with a numerical flag and summing.

LOAD customerString,

if(peek('customerString')=customerString,0,1) as FlagCustomer

resident yourDataTable order by customerString;

And the sum in your chart (should be way faster than count distinct).

Regards,

Stefan

[#5]

Hi Swuehl,

Is this the same as a counter? I already have this on my loading script as counter_patient.

Patient:
LOAD 
 1 as counter_patient
 ,PRFUniqueId as %KEY_PRFUID
 //PRFUniqueID AS PRFUniqueId
 ,PatientNo
 ,EDAccessNum
 ,ContactNumber
 ,AUTONUMBER(upper(anPatFirstNames)&upper(anPatSurname)&patientDOB) as UniquePatientID
    ,UPPER(anPatSurname) as anPatSurname
    ,UPPER(anPatFirstNames) as anPatFirstNames
    ,UPPER(anPatGP) as anPatGP
    ,UPPER(ethnicGroup) as ethnicGroup
    ,applymap('Gender', patSex) AS Gender
    ,patientDOB
    ,UPPER(IF(ISNULL(GPPracticeCode),anPatGPSurg,GPPracticeCode)) AS GPPracticeCode
    ,UPPER(patAddress) as PatientPostcode
//Calc Age from incDate Late
    ,IF(ISNULL(PatAgeYearsMonths),patAge,PatAgeYearsMonths) AS patAge
    ,TRIM(NHSNumber) as NHSNumber
    ;

[swuehl]

Hi Phil,

yes, it's kind of a counter replacement, but your current counter_patient will count (i.g. if summed up in a chart) every selected patient record, while we want to do a distinct count of uniquePatientID, right?

in our example:

LOAD UniquePatientID,

if(peek('UniquePatientID')=UniquePatientID,0,1) as distintCountUniquePatientID

resident Patient order by UniquePatientID;

I understood that you want to do a disinct count of your selected Patient records by UniquePatientID, correct?

Than in a chart, a

sum(distinctCountUniquePatientID) should be a good (i.e. way faster) replacement for the count(distinct UniquePatientID)

You may refer also to the manual, Part III (Diagrams), chapter performance optimization to get a deeper understand of my approach and also alternatives to this.

Regards,

Stefan