Data Loading: create a value when one-to-many table comparison finds no match

[toddbuss]

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

[vinieme12]

LOAD Distinct Patient_Number ;

JOIN

Load Distinct Patient_Number,Count(Diagnosis) as Num_of_Diagnoses

Group by Patient_Number ;

[tomasz_tru · Accepted solution]

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];

[toddbuss]

Thanks. I learned a new function with Alt() too.