Table vs chart different results

[dhborchardt]

I am getting different results on tables and charts if I call an access table. If I use inline data the table and chart are correct but as soon as I go back to the access table the chart is off. I have attached the qvw. I have each scenario on its own tab in the script editor.

[Oleg_Troyansky]

It only looks like you are getting different results, because the Table Object eliminates duplicates and only shows you distinct combination of fields, without counting rows. In the chart, however, all duplicated rows are counted.

You clearly have 39 rows in your Calendar table, and many duplications. The duplicate rows are caused by the INTERVALMATCH load.

In order to see the duplicates in the table, open Table Viewer (Ctrl-T), right click on the table and select "Preview". You will see raw data, and you'll be able to see all the duplicated values before any de-duplication.

[dhborchardt]

Oleg

Thanks for the reply. I was being thrown off by the inline example I was using. Both the table and Chart were the same. I moved the db data into an inline table and see that it is still off. In the end it is the table example that I need and the duplicates need to be eliminated. Can this be accomplished by changing the JOIN between the table?

Below is the data I have been using. I would like the result to come out to 15. I am trying to count each day a patient was in the hospital. As you can see the result for each patient is given. When these are added up they = 15.

AdmitDate, DischargeDate, VisitID
3/30/2011, 3/31/2011, V67 =2
3/31/2011, 3/31/2011, V68 =1
3/30/2011, 3/30/2011, V45 =1
3/30/2011, 3/31/2011, V10 =2
3/29/2011, 3/29/2011, V24 =1
3/30/2011, 3/31/2011, V81 =2
3/28/2011, 3/31/2011, V92 =4
3/30/2011, 3/31/2011, V37 =2

Total = 15

The chart should look like this

3/28/2011 =1
3/29/2011 =2
3/30/2011 =6
3/31/2011 =6

Total 15

Is IntervalMatch the best way to do this? I don't really understand what is going on with IntervalMatch.

[Oleg_Troyansky · Accepted solution]

Try something like this, it might be simpler and cleaner. The "WHILE load generates all the dates between admission date and Discharge date, for each VisitID - the same logic as your two joins, but perhaps cleaner.

Visits:
LOAD * INLINE [
 AdmitDate, VisitID, DischargeDate
 3/31/2011, V68, 3/31/2011
 3/30/2011, V45, 3/30/2011
 3/30/2011, V10, 3/31/2011
 3/29/2011, V24, 3/29/2011
 3/30/2011, V81, 3/31/2011
 3/28/2011, V92, 3/31/2011
];


DailyVisits:

load
 VisitID,
 AdmitDate,
 DischargeDate,
 date(AdmitDate + IterNo() - 1) as Date
resident
 Visits
while
 AdmitDate + IterNo() - 1 <= DischargeDate
;

drop table Visits;

[dhborchardt]

Yes, very clean and simple. Thanks.