Missing rows

[drohm002]

Hi,

My main table has 75,000 rows, one for each patient. I work in healthcare.  Another table has about 72,000 rows, one for each patient that matches the criteria built for that specific table, its filtered down a little.  When I load these two tables into my app, I only get the 72,000 patients to show, but I want to show all of the 75,000 patients.  I feel like i am missing something small somewhere for this to occur, does anyone have any idea?

When I click "Load Data" i can see that the app is fetching 75,000 rows for the first table, then it goes and fetches 72,000 rows for the next table.  These two tables have to be built seperately like I have them.  I dont want rows to drop off from the first table just because a patient/record doesnt exist in the second smaller table. Please help! Thank you

[TauseefKhan]

Hi @drohm002,

To ensure that all 75,000 patients from your main table are displayed, even if they do not have a corresponding record in the second table, you should use a Left Join. These operations will preserve all records from the main table while matching records from the second table based on a common key, typically a patient ID.

****Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****

[Vegar]

How are you loading the two tables.

Is it

load * from table1; 

load * from table2;

Or is it 

load * from table1;

Concatenate load * from table2;

Or is it 

load * from table1;

Left join load * from table2;

Or other? Please explain what you have done, then it's easier to help

[drohm002]

Hi Vegar, please see below how i am loading the two tables.  The Index Pop table is the one with 75,000 records.  Epic Payor table has 72,000 records.

See also the picture below showing the output of the two tables.  My count of total records should be the 73,483 number, but instead in my dashboard i am getting a total count of only the 72,784 records.  Obviously, there are many more tables in my data model, i know these two tables dont have a linked primary key.  I appreciate any insight you might have.  Thank you. 

[drohm002]

Hi Tauseef, please see my reply below to Vegar.  I would love to hear your input on that.  I used a left join in the transform layer, i dont think that is my issue

[rwunderlich]

"in my dashboard i am getting a total count of only the 72,784 records"

Could you explain more how you are displaying this count? Is it an expression, a chart?

-Rob

[drohm002]

Hi rwunderlich, see the KPI below and the formula used for it to get the 72,784 records.  I want this number to actually be the 73,483 number. I appreciate your help

[Fabiano_Martino_Intelco]

Hi @drohm002 

if you remove the DISTINCT in the expression how many Patients do you get?

Regards

[drohm002]

Hi Martino, if I remove the DISTINCT I get the same number as before, 72,781...unfortunately

[Fabiano_Martino_Intelco]

After loading the qvd in the tables do you perform other operations on the [Index Pop] table?