Opened 7 years ago

Closed 7 years ago

#198 closed Enhancement (Duplicate Ticket)

Populate Missing Values

Reported by: Lois Haggard Owned by: Testing
Priority: -Low Milestone: 2.1 - Testing
Component: View-App Version: Unknown
Severity: Minor Effort Keywords: query output
Cc:

Description

The new Populate Missing Values feature that was released in View 2.0 is not working as users would like. The code compares the returned dimension values to the complete list of dimension values and completes the results table with rows for any dimension values that didn't show up in the results. Here are the issues:

  1. The missing/supplied dimension rows appear at the bottom of the data list table, instead of integrated into the dimension list. Users who are trying to match up the results with an existing Excel table must manually rearrange the rows. It would be preferable to have the rows in the same order every time, regardless of whether they were missing.
  2. When a rate measure is used, the Populate Missing Values feature returns the 'missing' value (specified by the query content developer) for the main measure, only. So, for instance, the cells for the count, population denominator, and upper and lower confidence intervals are left blank. Not only does this look klunky, but the NM Tracking application that is consuming the NM-IBIS results is expecting those associated data elements, and breaks when it encounters a value in the xml without those elements.
    <RECORD>
         <DIMENSIONS>
              <DIMENSION>
                   <NAME>GeoCnty</NAME> 
                   <TITLE>Residential County</TITLE> 
                   <VALUE>
                   61 
                   <TITLE>61</TITLE> 
                   </VALUE>
              </DIMENSION>
         </DIMENSIONS>
         <MEASURES>
              <MEASURE>
                   <NAME>Rate</NAME> 
                   <VALUE>26.04</VALUE> 
                   <NUMERATOR>20</NUMERATOR> 
                   <DENOMINATOR>76809</DENOMINATOR> 
                   <LOWER_CONFIDENCE_LIMIT>14.63</LOWER_CONFIDENCE_LIMIT> 
                   <UPPER_CONFIDENCE_LIMIT>37.45</UPPER_CONFIDENCE_LIMIT> 
                   <RELIABILITY_FLAG>.</RELIABILITY_FLAG> 
              </MEASURE>
         </MEASURES>
    </RECORD>
    <RECORD>
         <DIMENSIONS>
              <DIMENSION>
                   <NAME>GeoCnty</NAME> 
                   <VALUE>3</VALUE> 
              </DIMENSION>
         </DIMENSIONS>
         <MEASURES>
              <MEASURE>
                   <NAME>Rate</NAME> 
                   <VALUE>0</VALUE> 
              </MEASURE>
         </MEASURES>
    </RECORD>
    <RECORD>
         <DIMENSIONS>
              <DIMENSION>
                   <NAME>GeoCnty</NAME> 
                   <VALUE>11</VALUE> 
              </DIMENSION>
         </DIMENSIONS>
         <MEASURES>
              <MEASURE>
                   <NAME>Rate</NAME> 
                   <VALUE>0</VALUE> 
              </MEASURE>
         </MEASURES>
    </RECORD>
    

We have played around with using a sql left outer join in the .def file so that if the dimension value is in the population dataset, it will be included. But this has a couple of problems:

  1. It works only for those variables that are in the population dataset.
  2. It is cumbersome code. It needs to check to see if either cross1 or cross2 is in the population dataset, and behave slightly differently, depending. The proc sql uses a coalese function that creates a new variable that must then be merged back into the IBIS .tmp result dataset. It is just complex and complicates the .def an awful lot. It seems to me it opens us up for human error in those pesky .def files.

I am wondering whether IBIS-Q could be programmed to handle this. I'm not sure how - because IBIS-Q does not have access to the complete dimension list - only the SAS variable. Although IBIS-Q could request the dimension file from the View app. There's a thought.

This is a sticky one. I'm hoping other community members will have some good ideas.

Attachments (1)

Populate_Missing_Values.png (45.0 KB) - added by Lois Haggard 7 years ago.
Data List table showing populated missing values.

Download all attachments as: .zip

Change History (7)

Changed 7 years ago by Lois Haggard

Attachment: Populate_Missing_Values.png added

Data List table showing populated missing values.

comment:1 Changed 7 years ago by Garth Braithwaite

Probably should reopen #126 as this is orig ticket so that we're not duplicating an existing ticket. Also this is related to the view/java code not IBIS-Q.

comment:2 Changed 7 years ago by Garth Braithwaite

I've tried sorting and grouping dim name and value with/wo title and a mix and it's not 100%. If I use the dim value to sort on the order isn't correct as this element is defined to be character so 1< 11 < 2 < 21 < 3 < 5 etc. I can use the VALUE/TITLE which has it's own issue with sorting as the total row will show up somewhere in the body of the table. I can't simply do a nested loop based on dimensions because we can have 1:n dimensions. So at this point the best solution is to employ the jquery table sorting. Lois has mentioned implementing a SORT_ORDER element like IPs have which would take care of everything - but would require all module dimensions needing this to be edited. See ticket #108.

Last edited 7 years ago by Garth Braithwaite (previous) (diff)

comment:3 Changed 7 years ago by Garth Braithwaite

Component: IBIS-QView-App
Milestone: Unassigned2.1 - Testing
Owner: set to Testing
Priority: Unknown-Low
Severity: UnknownMinor Effort

comment:4 Changed 7 years ago by Eleanor Howell

Keywords: query output added

comment:5 Changed 7 years ago by Tong Zheng

Lois is going to open another ticket for sort order and missing value ticket. This ticket is closed.

comment:6 Changed 7 years ago by Tong Zheng

Resolution: Duplicate Ticket
Status: newclosed
Note: See TracTickets for help on using tickets.