Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Pardon my lack of database parlance. I think I have a pretty basic problem, but I don't know how to search for a solution.
I have two tables: patient number and diagnosis codes&sequence. Each patient could have between zero and nine diagnoses. I'm able to load and count the values just fine, but i'm having trouble identifying all the patients with zero matches on the diagnosis code&sequence table. They turn up as null in my visual, but null is not value that can be selected in QlikSense.
I don't think this situation warrants the use Applymap() because of the one-to-many relationship. Please correct me if i'm wrong.
Here's a simplified version of what I'm working with:
[patient table]
Patient_Number
100
101
102
[Diagnosis Table]
Patient_Number, Diagnosis, Sequence
100,"depression",1
100,"anxiety",2
// note patient 101 has no diagnoses.
102,"depression",1
[Desired Result Table]
Patient_Number,Number_of_Diagnoses
100,2
101,0
102,1
Thanks in advance for any helpful tips.
--Todd
Try this:
[patient table]:
load * inline [
Patient_Number
100
101
102
];[Diagnosis Table]:
load * inline [
Patient_Number, Diagnosis, Sequence
100,"depression",1
100,"anxiety",2
102,"depression",1
];[Desired Result Table tmp]:
NoConcatenate LOAD Patient_Number RESIDENT [patient table]
;
JOIN
LOAD Patient_Number, Count(Diagnosis) AS Number_of_Diagnoses
RESIDENT [Diagnosis Table]
GROUP BY Patient_Number
;
[Desired Result Table]:
NoConcatenate LOAD Patient_Number, Alt(Number_of_Diagnoses,0) AS Number_of_Diagnoses
RESIDENT [Desired Result Table tmp];
DROP TABLE [Desired Result Table tmp];
LOAD Distinct Patient_Number ;
JOIN
Load Distinct Patient_Number,Count(Diagnosis) as Num_of_Diagnoses
Group by Patient_Number ;
Try this:
[patient table]:
load * inline [
Patient_Number
100
101
102
];[Diagnosis Table]:
load * inline [
Patient_Number, Diagnosis, Sequence
100,"depression",1
100,"anxiety",2
102,"depression",1
];[Desired Result Table tmp]:
NoConcatenate LOAD Patient_Number RESIDENT [patient table]
;
JOIN
LOAD Patient_Number, Count(Diagnosis) AS Number_of_Diagnoses
RESIDENT [Diagnosis Table]
GROUP BY Patient_Number
;
[Desired Result Table]:
NoConcatenate LOAD Patient_Number, Alt(Number_of_Diagnoses,0) AS Number_of_Diagnoses
RESIDENT [Desired Result Table tmp];
DROP TABLE [Desired Result Table tmp];
Thanks. I learned a new function with Alt() too.