Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

toddbuss
New Contributor III

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

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

1 Solution

Accepted Solutions
tomasz_tru
Valued Contributor

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

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

3 Replies
vinieme12
Esteemed Contributor II

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

LOAD Distinct Patient_Number ;

JOIN

Load Distinct Patient_Number,Count(Diagnosis) as Num_of_Diagnoses

Group by Patient_Number ;

tomasz_tru
Valued Contributor

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

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

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

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

Community Browser