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,
Check by TRACE statement:

// After loading, trace the number of rows loaded for 'EMPI'
Let vEMPIRowCount = NoOfRows('YourTableName');
Trace Number of rows loaded for EMPI: $(vEMPIRowCount);

[Pablo007]

Hi,

Maybe Will be interesting if you can show to us a printed screen of Data model viewer

To see if there is any relationship between the two tables.

So to speak, some times If this is the case the script can cut your data just to ones that are on both.

[drohm002]

I dont do joins and dont do any functions in my complete app later, i simply load in the tables.  This issue is very perplexing to me.  I know there are patients in the Index Pop table that arent in the Epic Payor table, and those patients fall off but i dont want them to

[drohm002]

Hi Pablo, see a portion of my data model viewer below.  The Epic Payor table is the culprit here, only patients that exist in the Epic Payor table are showing up in the Index Pop table.  I want every patient to show up in the Index Pop table regardless if they are in the Epic Payor table...

[marksouzacosta]

Hi @drohm002,

My 2 cents here: it is not recommended to use link fields in your chart measures, this can lead to bad results. So, create an EMPI Counter field in your Index Pop Table - try EMPI AS [EMPI Counter] - and use this field in your measures.

Now, if you need to aggregate every single EMPI in one single table, you will need to do extra manipulation your Load Script - with joins, concatenates, etc.

In any case, keep that in mind: don't use link fields in your measures.

[drohm002]

Hi Mark, thanks for the info.  That unfortunately did not work though

[drohm002]

Hi Tauseef please see the screenshot below, i did what you described...

[marksouzacosta]

Can you please share your QVF application without data? I think you are reducing your records somewhere.

[drohm002]

Hi Mark, yes my records are getting reduced because of the Epic Payor table, that is the culprit, see the screenshot below.  Im not understanding why my Index Pop table gets reduced down to only the patients that exist in the Epic Payor table.  When I count up EMPI's i want it to be the 73,438 number that we have in the Index Pop table whenever it is loaded and it fetches 73,438 rows...

[Fabiano_Martino_Intelco · Accepted solution]

Hi @drohm002 

the TRACE right after the load confirms that you are loading 73,438 rows.

Can you also place the code block:

Let vEMPIRowCount = NoOfRows('YourTableName');
Trace Number of rows loaded for EMPI: $(vEMPIRowCount);

as the last instructions in your script?

This will show the row number in the table at the end of the operations.

Also, @marksouzacosta suggestion is correct. Usually you don't count keys. I suggest you create a parallel field and use function such as count() on that.

Having the QVF (without data) or the script could help analyze your problem.

Regards