Home

InfoFlex Query Design Manager User Guide

1. The view name is a Options Sot Group By Sle Clinical Data view i Fupetien Data lterns Format Options Sort Group Ey Hide Alias A Analysis Grou displayed in the titlebar Ta a GLOBAL Hospital Number O system 7 Surname o T Views an Forename O O e E Mp view Date of Birth dd mm yyy O o Appointment date drain O O Date of Reterral dd mm yyyy O O Consultant Fel Code and Meaning O O r gt Source of rral Code and Meaning O O E Select an item in the k grid and it will be highlighted in the Items eee tree are ira Category a EJ Date of Referral pane EJ Date referral receiwed E 1st available appointment fae 1 Days from referral to appointment July 2015 Page 17 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 2 1 3 Reviewing filter definitions When you select a filter on the Filters tab the name of the filter is displayed in the titlebar of the main window and the items that belong to that filter are displayed in a grid beneath the titlebar You should always double check that the correct name is displayed in the titlebar to ensure that you are viewing the correct filter The Items tree displays all the items in the current domain or data view Note that when working in a domain the Items tree displays all the events and items in the domain When working in a data view the Items tree displays the events panels and
2. w Output bo Grid Output to File Qutpuk to Excel Code List Manager Delete Data Refresh Calculated Items Ghange Nonrepeat Event to Repeat Reset Cached Definitians QDM can also be launched from toolbar buttons in the Design Management and Data Analysis modules Ki Launch Query Design Manager QDM displays one domain or one data view at a time You must select the domain or data view in which you want to define your queries before you open QDM When QDM is opened it displays either domain queries or data view queries depending on the context from which it has been opened When opening QDM from the Design Management module QDM displays the domain or data view which is currently selected E DOMAIN E 3 DATA VIEW a DATA VIEW pE Clinical Domain i Audit Data view Clinical Data view E El YE Clinical Data view aa Fl Referral r ae P B E i EEA A A m L AO AF A dinin Surgical E ve Quer Design Manager Query Definition IO x ications Sure py Event r ications treat File Edit view Options Queries vie s Filters Queries QDM is displaying Clinical domain queries because the Clinical domain is currently selected July 2015 Page 7 Chameleon Information Management Services Ltd InfoFlex QDM User Guide DOMAIN 4 gt DATA VIEW EA gt DATA VIEW Clinical Domain Audit Data view Clinical Data view vn Clinical Domain Alf pal e i Clinica
3. July 2015 Page 1 Chameleon Information Management Services Ltd InfoFlex QDM User Guide CONTENTS FP DOUC THIS document corcoesto aa a a 1 About Query DesignManager ssssscccccccsssssccecccccsssseceeoccsssssseeeesssssse O LE A out Oner Desi Maina eE aaa a E E cates 6 L2 Domamand data View QUELICS aiaa TE Haereesethitveangtecenteei eases 6 Ey ACCESS mE ODM aena a T titra astenentessh eas eases 7 bA QUY GROUPS ane e a A eascetectteoa aus 9 lA Query oroup 1A OMAN edea a a 9 42 Query stoups ina data VIEW incuan E a 10 1 4 3 Viewing query groups belonging to other users cecccceessssseseeeeeeeeeeeeeeees 11 Bos Denning guem Srono oroa aE E 13 LOS EEEE ooe E AAE E E AEA 14 2 About g eriCS rcii searen N a a LA Sr Navea oM a S a a Gute 16 21I Revewimne guery GEMNIMONS reesi ne a E ESk 16 2l2 Reviewine view definitions aerae a a aE 17 21 3 WReviewine filter definitions maior EE 18 22 EKN Eei e a a a a a eupaacd aaa 19 3 Definite View Secseca aaee N Jl Creating a Vie Werne n a a E 20 3 2 Adding items to the VieWpraiecrioreni nn n a 21 Jal Reorderine 1LCIIS St acer tones tere Sener sateen ect syvseevec et tee on eueseat Meee suite danhs 22 Bio Viewer PrOperui s aurkia a Sisal ob a e aaa a 23 Aok Formas eci e tiaaincicucs Mes eideaandeue 23 IL OPO sonr detent deatamesteltnetcucens 23 ae e Oa A AEE E E I E AA EE A 23 KoA COUN Daa A acess 23 oa He a a cee ny ine Snr a ceaen nny renee rarer nn WERT 23
4. July 2015 Page 100 Chameleon Information Management Services Ltd InfoFlex QDM User Guide After making your selections press OK to export the data A confirmation message confirms that the data has been exported IfxExporter x J SUCCESS 17 records exported You can now open and review the exported data file July 2015 Page 101 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 12 MOVING AND COPYING VIEWS FILTERS AND QUERIES Views filters and queries can be cut copied and pasted You can use this facility to copy or move views filters and queries between query groups Cutting and pasting a query will move it from one group to another Copying and pasting a query will make a copy of the query in a new group or in the same group To cut or copy right click the view Queries Views Filters filter or query concerned and choose Cut or Copy I Clinical Data view Expand Expand Branch Expand Tree Mew Quer Group Delete Query Group ew Rename Delete W Paste Chri To paste the query right click the group name of the group you wish to copy or move it to and choose Paste Collapse Expand Branch Expand Tree New Query Group Delete Query Group Mer Rename Delete A Cut Ctrl Y Copy atra Fa July 2015 Page 102 Chameleon Information Management Services Ltd InfoFlex QDM User Guide Quenes Views Filters The new view is displayed in th
5. T EE July 2015 Page 25 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 4 2 Filter criteria The Query Filter Properties section of the screen displays the criteria that have been defined in the filter Each filter criterion is made up of an item an operator and a value The operator specifies how the data item is compared with the value For example a filter criteria to return male patients would be defined as Query Filter Properties Referral filter comparison value 4 2 1 Adding items to the filter To add an item to the filter ensure that the correct filter is selected then open the Items tree to find the items that you wish to add Note that when working in a domain the Items tree displays all the events and items in the domain When working in a data view the Items tree displays the events panels and items that exist in the data view that you are creating the filter in Or a Te ll seve Clini irg EP pe Clinical D ata View a e GP Details E a g Referral r bess E Appointment date e Details wa Hospital Name Appointment date S al Hospital Name Items tree in l a4 Consultant a Consul PA s na Items tree in oreuiltart ae ab Title the data view B T Source of Referral oe Ta Source of Referral fp TE Category _ i Categor aad fe Date of Referral E e facie aa nw B Date referral recen ed E ne See ee A 1st available
6. July 2015 Page 82 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 9 3 Concatenation Create a view which concatenates Forename and Surname into one column Note that Access databases require amp and SQL databases require Create a second view which concatenates Forename and Surname into one column and include a space between the Forename and Surname fields 9 9 4 Fixed values Make a copy of the Referral view and put it in the Functions exercises group Edit the copy of the Referral view Add a new blank column to the view Name the column My blank column Add a second column which contains the word Hello Name the column My fixed value Make a copy of the Referral query and put it in the Functions exercises group In the copy of the Referral query select the copy of the Referral view Run the copy of the Referral query and ensure the two columns are displayed 9 9 5 Expressions in views Recreate the examples in section 9 6 The examples are all taken from the Clinical data view and use data items from the Patient registration and Referral events Test the views in a query using the All patients filter 9 9 6 Simple calculations Recreate the examples in section 9 7 The examples are all taken from the Clinical data view and use data items from the Patient registration and Referral events The Weight items can be found in the Medical History panel of the Referral event Test the views i
7. 0170171 900 0170171900 yes no Note that in SQLServer the IFNULL statement is used to set an alternative value where the item is Null The alternative value set by the IFNULL statement must match the item type The IF statement is then used to set yes when the alternative value is present and to set no otherwise The formula for the Surname item was created by adding the Surname item to the view then opening the formula builder and building the formula as shown below ioja TIF IFNULL Fatient Registration Surname no data no data yes no E Seer ea a A rae a cea ee z e Patient Registration Surname O o O no data E O no data o t yee O 4 HOn io qP The formula for the Date of Birth item was created by adding the Date of Birth item to the view then opening the formula builder and building the formula as shown below ox IIF IFNOLL Patient Registration Date of Birth O1 01 1900 rr ro rr 0170171900 Cl IIF IFNULL Patient Registration Date of Birth O1L OL S L900 Ol OL L9S00 OKOLO KO AL July 2015 Page 76 Chameleon Information Management Services Ltd InfoFlex QDM User Guide These results are returned SQL Results 29 rows 123456 WES 01701 1945 no GEBGBG Smith ro 01701 1950 no 111111 Smith no Ves fees Jones Ho VES 4339533 WES 01701 1982 no 444444 Jones no Ves D90599 WES 01701 1950 no tier
8. 1 record is returned as there is only 1 record where the criteria are met within the same Referral Set the Link level to the Patient Registration event then save and run the query With the link level set at the Patient Registration level the filter criteria can be met anywhere within the patient 12 records are returned This is because using the Admission and Radiotherapy events from anywhere in the subject overview there are 4 different ways in which the criteria can be met and these are listed for each of the 3 Referral events Order the grid by Appointment date to see this more clearly The subject overview for the patient in question 1s as shown below If you wish to review the data go to Data Entry and in the Training data view search for the patient with a Hospital Number of LINK TEST PATIENT 7 Patient Registration 7 Referral 7 Admission Date 01 01 2000 7 Radiotherapy Type Type A Meets criteria only when link set at Patient Registration level Meets criteria when link set at Referral level L Referral L Admission Date 01 01 2001 7 Radiotherapy Type Type A Meets criteria when link set at Patient Registration level L Referral 7 Admission Date 01 01 2002 7 Radiotherapy Type Type B July 2015 Page 52 InfoFlex QDM User Guide Chameleon Information Management Services Ltd 7 3 Distinct Rows As we have seen above the number of records returned matches the number
9. These results are returned SQL Results 15 row s 890123 Johnston 01 06 2000 06 06 2000 5 BBBEEE Smith 20 12 2002 20 01 2003 31 234567 Jones 30 05 2000 01 07 2000 32 567890 Clarke 05 03 2000 05 05 2000 61 July 2015 Page 78 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 7 Simple Calculations in Views Simple calculations can be carried out in views as described below Note If calculations are regularly used in Data Analysis it is worth considering creating the calculation in a calculated item in the design This will simplify the view and improve the performance of the analysis Note The function builder should be used to carry out calculations wherever possible In particular date calculations should always be carried out using functions rather than simply subtracting one date from another This is because you can choose the unit that the result is calculated in days months hours etc Also exceptions such as missing and unknown dates are handled better 9 7 1 How to define calculations in views If the view is the appropriate place for the calculation the following operators can be used in the calculation plus minus multiply divide brackets NOW function numeric values Note that brackets should be used around the calculation to distinguish it from other view items It can be helpful to display the SQL on the Views tab see section 5 3 3 whilst creating calculations in order to be su
10. AB100 St Matthew s Hospital 222222 Brown 01401 1945 99999 1 ARE hella own 234567 Blank columm 30 05 2000 1 ARE hella 1 Fixed value column k s Hospital J3343 alata 99959 99999 Missing hello n s Hospital 345678 Rose 01 01 1925 10 07 2000 2 Dentist hella 71230011 Jones BC200 St Mark s Hospital 456789 Gate 01401 1955 01 07 2000 0 GP hella 71230009 Hughes BC200 St Mark s Hospital L July 2015 Page 70 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 6 Expressions using the Formula Builder Expressions can be added to views by using the Formula builder After adding an item to the view press the fx button Query View Properties my view Hospital Humber Surrnarne Date of Referral The Edit Formula dialog is displayed and you can create a formula in the usual way Note that a smaller range of functions is available than is found in Design Management This is because the expression must be turned into SQL and passed onto the database to evaluate rather than being evaluated directly by InfoFlex ioj Referral Date of Referral e Cancel July 2015 Page 71 InfoFlex QDM User Guide Chameleon Information Management Services Ltd Example 1 This example uses the DateDiff function to calculate the difference between the Appointment Date and Date of Referral in the view Add the Date of Referral item to the view and press the fx b
11. Note that the Document query group and Subject Search query group are attached to the domain Some other query groups have been defined within these groups Expand the Queries heading attached to the Subject Search query group Note the difference between the symbol for a query and the symbol for a query group Close QDM In Design Management select the Clinical data view then go to the Actions menu and choose Query Design Manager Note that QDM displays the Clinical Data view Note that the Analysis query group is attached to the data view The Analysis query group contains the Global group and the training group Create two query groups within the training query group Call them Training group 1 and Training group 2 July 2015 Page 14 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 2 ABOUT QUERIES A query combines a view and a filter Views define which items of data will be displayed for the chosen group of patients A view can contain multiple data items from multiple events Functions can be applied to items in the view for example to show the maximum minimum or average of an item and calculations can be carried out within a view for example to add two values together WY Filters define the subset of data that is to be returned i e which group of patients you wish to view for example all male patients or all patients with a certain referral date or a certain consultant Filter criteria can be
12. Query 4 1 oo Fey Quen 4 2 July 2015 Page 10 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 1 4 3 Viewing query groups belonging to other users By default within a data view each user can view their own named query group in addition to the Global query group In addition Administrators can choose to view query groups belonging to other users To view another user s query group open QDM for the appropriate data view Query Design Manager Query Definition File Edit View Options H oela mY Queries VIEWS Filters ME Suey Properties Perry a I 7 Property El Analysis Group a D i ee GLOBAL 2a ion A Global group 1 View ie Queries Filter El Global group 2 rine a T am Outer Join No jen system 1 Queries Distinct Rows Mo a BR My Quer Context a Query 101 User Defined No ie Fy Queny102 File Edit View Options Queries A list of users is displayed Select which users groups you wish to view Select Users x Selected Department al asmith berth csrith dsrmith esmith L L E famith L L CI gamith i mith BS ear El July 2015 Page 11 Chameleon Information Management Services Ltd InfoFlex QDM User Guide After pressing OK the selected users groups are displayed in QDM Query Design Manager Query Definition File Edit View Options Bit elamy E Quenes WIEWS Filters CME 8 6Lfm Emo Proper
13. Replace delimiters in data with Replace Unknown 88858 with Replace Missing 99999 with In the Filename box press the button Navigate to a folder location and give the file a name Filename C 4infoFlex v5 Data My data txt On the tabs make selections about the format that the file should be in Property Column Delimiter Should the file be column or tab delimited double quotes placed at the start and end of each item of data space space recommended replace it with with replacement text Tick if you wish to include column headings in the file earch detain attached to each column heading Suffix Enter any suffix to appear after the column headings ie not a a ee on the Main tab N Di eee and dictionary items OO a If you wish enter a character that should appear at the start of every piece of data Replaces Use quote marks Replace Unknown 88888 If you wish to replace 88888 in the data tick then type the with replacement text Character at end of data If you wish enter a character that should appear at the end of every piece of data Replaces Use quote marks Enclose column headings Tick if you wish the start and end characters to be applied to Po File Footer Enter footer text which will be the last row of the file po RowPrefix Enter prefix text which will appear at the start of every row_ P Row Suffix Enter suffix text which will appear at the end of every row
14. Te 50 Qin eee eae eee BEA User defined SQL p 5 x Fie Options SELECT Note that where a coded item is included in a SELECT statement the Coded Items option on the Options menu controls the format that the data is returned in iok File Options ee Show Code Only SHE Meaning Only w Show Code and Meaning July 2015 Page 58 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 8 VIEW PARAMETERS OCCURRENCE COUNTING 8 1 Occurrence Counting Occurrence counting counts the number of times that each value entered in an item occurs This function can be used for example to count how many times each source of referral has occurred Occurrence counting has to be used with the Group by function To count the number of times that each item value occurs add the item that you wish to count to a view and tick the Group By column Query View Properties Source of referral Count pee faa e a Oee Sons Hide Alias La Source of Referral Code and Meaning To O Right click the item and choose Add Operator then Count Query Yiew Properties Source of referral Count i ee Hide Alias La Source of Referal Referal Bd Code and eaning LJ Delete Add Operator F Plus Minus Multiply Divide Open Bracket Close Bracket Value Expr Mow Count s A second row displays the Count command Query View Properties Source of referral Count Source of R
15. 01 01 2002 7 Radiotherapy Type Type B July 2015 Page 49 Chameleon Information Management Services Ltd InfoFlex QDM User Guide The link level specifies whether these criteria must be fulfilled within the same Referral event or whether the criteria can be fulfilled across different occurrences of the Referral event When determining which Referral events meet the criteria if the link level is set at the Referral event the Referral event is the Common Parent and in order for a particular referral event to be returned the filter criteria must all be met in child events of that referral In the example above the criteria are only met within Referral B if the link level is set at the Patient Demographics event the Patient Demographics event is the Common Parent and Referral events can be returned as long as the filter criteria are met somewhere within the patient In the example above all the Referral events would be returned So with the link level set at SQL Results 1 rowfs Referral only one record is Hospital Humber Date of Admission Type of radiotherapy returned LINE TEST PATIENT 01 01 2001 01701 2001 O Type A But with the link level set at Patient Registration 12 records are returned This is because there are four combinations of ways that the filter criteria can be met within the subject overview and these four ways are listed once for each of the three Referral events SQL Results 12 row s
16. Appointment date Date of referral From the Referral event Assessment Details panel Presenting Symptoms Initial Diagnosis Set formats for the date items Set formats for the dictionary and coded items choose code amp meaning Save the view July 2015 Page 24 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 4 DEFINING FILTERS Filters allow you to specify a particular set of patients that you wish to view data for You can set up a filter that contains only one criteria eg all female patients or all patients for a particular consultant or you can set up a filter that combines several different criteria eg all male patients of a particular age that had a particular type of surgery 4 1 Creating a filter Queries Views Filters Filters ih a Data WEL To define a filter go to the Filters tab right click the query group you wish the filter to belong to and choose New Expand Branch Expand Tree New Query Group Delete Query Group TN Malaka A new filter is displayed in the query group Queries Views Filters It is represented by the symbol ip Tapa Data WIEN Queries Views Filters Type a name for your E 2 Data view filter Itis displayed next to the symbol and the name is also displayed in the titlebar of the main window You are now ready to add criteria to your filter Query Filter Properties Referral filter Data tem Operator
17. E E1 cow TF My filter i eal group 1 i a group 2 Elec group J F system E my group 1 FA my group 2 a my group 3 aiis Fh My query oad group 1 lees group 2 fF Ey group 3 EJ system iad my group 1 oo my group a Paeria my group 7 2 1 1 Reviewing query definitions Whenever you select a definition in the navigation tree on one of the tabs in Query Design Manager the contents or properties of the definition you have selected are displayed in the main section of the screen bordered in blue When you select a query in the navigation tree on the Queries tab the view filter and query parameters of the query are displayed The name of the query is displayed in the titlebar of the main window You should always double check that the correct name is displayed to ensure that you are viewing the correct query Query Design Manager Query Definition File Edit View Options H eelam telz Queries VIEWS Filters Te hhh Query Properties My Query 5 Cinical 0 The query name is Value EEE Ang displayed in the titleb seription isplayed 1n the titlebar P l Al view hy view 7 Filt My filt a ilter y filter a Ty Link at Outer Join Ho Distinct Rows Mo Context User Defined Mo If you have selected a query on the Queries tab then when you move to the Views tab it will display whichever view is used in the selected query Similarly if you move to the Filters tab it will display wh
18. E S i Seventies E S E E N S EE NE E E E 54 Jabs JOME sarena a a a aA 55 TAM HOw toser he Toser ie a 56 ed EKOCI S Eno E E E O uenaied 57 FI COET ao T sarutaase haa eaddanteees eta noaenaae 58 TO Userde TIMED 55k cas tseatousteaera neat casan eae cebianicc ee asinasee ei ademneee ame teak 58 View parameters Occurrence Counting cccccssssssssscccccoees DD Bel Qee nee COUMUN 0 iesea E 59 S2 EKETE OE oiiaii AEA EA A AEEA 6l Manipulating Data in Views sssssssscceccccssssscceeococsssssceceseossssseeeeeess OD S PUNCH ONS eia AT T A Aelia 62 Dill EO ene vert crane A T E E EN 62 92 Count DIS MECN skaran a e a 63 Deo MI eaa e EE E EE E sao te lbceastin dosatesntentons 63 Mee Mar esene E ieaes biaeia eenaen 63 DMRS SEMIN gated cers ies hte tec naib E E E tetera asides 64 Di AV cccin iuccadtiess tebe a etek Ok dialed eek eared ted te aes 64 ON SDE eae ea ai E EEA teri aloe ines TO 64 gS Medan kaara e a A a E T AN 65 IR AD e e a a E aduteeadncn at enn 65 PLNO UPPE LOWE aeea e TET 66 OUD Crm R n A a A a a 66 92 IWMI PIC funcions nasosi a E 68 9 3 Aggregated values grouped by patient eensnesssssssseeseeeereresssssssssssssserereeo 68 OA AC ONCALS MATION sesa canes A AEE snaaaaeatansne aa pontan 69 95 Fixed VANES 1 Views onion E aE anaes 70 9 6 Expressions using the Formula Builder nnnnnnossssoosooeeereesssssssssssssseeeereeo 71 9 6 1 Expressions without the formula builder cc cceccccc
19. Query Filter Properties Blank memo item filter P Memo LIKE For each of these rows add an expression then type the text shown into the Value column To retrieve memo items that are not null or not empty the following syntax should be used Query Filter Properties Hot blank memo item filter P Memo NOT LIKE For this row add an expression then type the text shown into the Value column July 2015 Page 36 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 4 8 Exercise 1 In the Training group 1 group create a new filter called Referral filter Add the following items From the Referral event Details panel Date of referral In the Operator column select gt In the Value column enter 01 01 2000 Save the filter 2 Create a filter to find patients who are both female and have the GP with code X9870004 3 Create a filter to find patients whose surname is Smith or Jones and who are aged over 60 use the Age item on the Assessment Details panel of the Referral event 4 Create a filter to find Referrals where the source of referral is O GP 1 A amp E or 2 Dentist 5 Create a filter to find Referrals where the patient s Presenting Symptoms include 3 nausea 6 Create a filter to find Referrals where the Source of Referral is marked as Missing July 2015 Page 37 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 5 DEFINING A QUERY 5 1 Crea
20. Query Properties link Description Whew Filter Link at Outer dorn Distinct Rowe Context User Defined link link Referral m X Patient Registrati H G7 Referral te By default no link level is shown within a query definition and InfoFlex uses the lowest possible common parent as the link level July 2015 Query Properties link Property Description View link Filter link Link at Outer Join Ho Distinct Rows Mo Contest User Defined No Page 51 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 7 2 2 Exercise This exercise recreates the example described in section 7 2 above In the Training Data view create a view called Link example as follows From the Patient Registration event Hospital number From the Referral event Details panel Appointment date From the Admission amp Surgical event Surgery Details panel Date of Admission From the Radiotherapy event Date of this radiotherapy Type of Radiotherapy Create a filter called Link example as follows Date of Admission gt 01 01 2001 Admission amp Surgical event AND Type of radiotherapy Type A Radiotherapy event Create a query called Link example and select the Link example view and the Link example filter Set the Link level to the Referral event then save and run the query With the link level set at the Referral level the filter criteria must be met within the same Referral event
21. appointment 1 D aps from referral to appoirtr EJ t available appointment n np age item reste 1 Daye from referral to appointr Assessment details ite Image item Surgery Details B m 1 Age ae Complications post Surgery B fry Presenting Symptoms Tbe Other management details E al Other presenting symptoms Bs Returm to theatre details E a 1 Duration of symptoms Ee Treatment Details B i Performance Status Be Medication Details B i ab Initial diagnosis H Treatment complications B i E Date of Diagnosis Hs Radiotherapy details B ae Chemotherapy Details B 5 ee a Admission amp Surgical Event r d 4 a nate i Basis of Diagnosis F for Surgery July 2015 Page 26 Chameleon Information Management Services Ltd You can add items either by Queries Views Filters InfoFlex QDM User Guide e dragging them from Ee aT the Items tree into the Ga Domain Dataltem Operator Query Filter Properties box Or by pe Y Consultant prompt pase W Females o o W Males e double clicking the fo o ke MY Referral date prompt item in the Items tree J E ltem Appointment date Drag and drop or double click an item at Hospital Hame at Consultant ab Initial a Title Ta Source of Referral Ry Categoy k EC ate o of Referral t Date referral received qat available appointment Notice that as you add items to the filter t
22. defined from any event in the design and multiple criteria can be defined within one filter You can also define filters which prompt the user for certain criteria when the query is run eg you can prompt for a consultant name or for a date range Prompts reduce the need to define multiple queries based on the same data item 4 Queries join a particular view and filter together The resulting query returns a set of data which meets the view and filter criteria Query parameters can be set to control the behaviour where view items or filter items come from multiple events Joining controls whether a subject can be returned if not all the events represented in the view exist for that subject Linking controls which records are returned when filter criteria come from one or more repeat events These parameters will be explained later in this document July 2015 Page 15 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 2 1 Navigation In Query Design Manager there are three tabs the Queries tab allows you to view create and edit queries the Views tab allows you to view create and edit views the Filters tab allows you to view create and edit filters Any structure of groups that you create is visible on each of the view filter and query tabs Queries views Filters Guenes Views Filters Queries Views Filters Queries Eee ipl Clinical Data view Analysis Group E E eee
23. in sity of thyroid and other endocrine glands 4333533 Joseph 01 01 1985 99999 99999 Missing 901 234 Miles 07 01 1980 08 01 2000 1230002 Jones O GP J039 Acute tonsillitis 675901 Mount 01 01 1945 02 08 2000 21230013 Brown 1 ARE Szr Traumatic haemothorax foo 2 Parisien 017 0171925 02 04 2000 21230017 Green 3 General Surgeon J359 Chronic disease of tonsils and adenoids 345678 Rose 017 0171925 10 0772000 21230011 Jones 2 Dentist C22 Malignant neoplasm of subgloths Smith 0170171940 2071272002 21230006 Adams 2 Dentist July 2015 Page 41 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 5 3 1 Studies and queries When a query is run from a domain you are prompted for a data view to run it against The data view filters the results so that only those events that are flagged as belonging to any of the studies in the data view s study list are returned Therefore if events represented in the view exist for a patient but the events do not belong to any studies represented in the selected data view those events will not be returned in the query results fl Select Dataview x pales oman OF Cancel 5 3 2 Testing query results When you are defining a query for use for example in a report you should always run it in QDM first to ensure that the query is returning the results you expect For testing purposes it can be helpful to include in your view the items that are being used i
24. of combinations in which the filter criteria can be met within the events regardless of which items are included in the view In the example above with the link level set at Patient Registration 12 records were returned Since the items in the view come from several repeating events at the same level each record returned represents each different combination of the data With the filter described above in the Link example these results are displayed SOL Results 12 row s Hospital Number Appointment date Date of Admission Date of this radiotherapy Type of radiotherapy LINK TEST PATIENT LINK TEST PATIENT LINK TEST PATIENT LINK TEST PATIENT LINK TEST PATIENT LINK TEST PATIENT LINE TEST PATIENT LINE TEST PATIENT LINK TEST PATIENT LINK TEST PATIENT LINK TEST PATIENT LINK TEST PATIENT 01 01 42000 01 01 42000 01 01 42000 01 01 42000 0170172001 0170172001 0170172001 0170172001 0170172002 0170172002 0170172002 0170172002 0170172001 01 01 2002 0170172001 0170172002 0170172001 0170172002 0170172001 0170172002 0170172001 0170172002 0170172001 0170172002 0170172000 0170172000 0170172001 0170172001 0170172000 01701 2000 0170172001 01 01 2001 0170172000 0170172000 0170172001 0170172001 If the view only contained items from the Patient Registration and Referral events 12 records would be returned and some of the records would look the same since the different data items that distinguish t
25. subfilter l Create a new query group called Subfilters 2 Make a copy of the Prompt filter and paste it in the Subfilters query group 3 Create a new filter in Training group 2 called Combined filter 4 Switch the Items list to display filters Add the copy of the Prompt filter in the Subfilters query group to the Combined filter 5 Add the additional criteria AND consultant prompt to the filter 6 In Training group 2 create a view containing Date of Referral and Consultant 7 In Training group 2 create a query using the view and filter you have just created Run the query and check that the correct results are returned Test it with Date of Referral lt 01 01 2000 and gt 31 12 2000 and Consultant Z1230002 10 7 4 Subqueries This exercise recreates the example in section 10 6 above 1 Create a new query group called Subqueries 2 In the Subqueries group create a query called My Subquery The view should contain the Hospital number only The filter should specify Presenting Symptoms CONTAINS CODE 3 nausea Run the query and ensure three records are returned 3 In Training group 2 create a filter called Symptoms Set the criteria to Hospital number IN My Subquery AND Performance status 0 Fully active 4 In Training group 2 create a view called Symptoms Include the Hospital Number Presenting Symptoms and Performance Status 5 In Training group 2 create a query called Symptoms and set the Symptoms view an
26. the value If they need to supply a different value from the default then they are able to change the value as normal July 2015 Page 46 Chameleon Information Management Services Ltd InfoFlex QDM User Guide The prompts will work in Worklist Data Analysis Reporting Documents Subject Search Queries etc They also work with add ins such as the Summary Doc Addin and the Correspondence Addin and Extract Addin The batch process with the profile that generates prints emails reports does not allow prompting Although default values are usually used automatically the information functions above are user based and will not work here The Multiple Reports add in is not able to make use of these default values Parameter name The Parameter Name column is for use if the prompted value needs to be printed on a report The parameter name is included as a document item on the document template and is substituted for the prompted value when the report is generated See section 6 1 of the Report Definition manual The Parameter Name column can also be used if the same parameter is used more than once in the filter If the same parameter name is used against each instance of the parameter it will only be prompted for once The entered value will automatically be used in each subsequent occurrence of the parameter without further prompting Running a query containing a prompt filter When a query containing a prompt filter is run a Parameters b
27. value column of the filter 4 7 4 Filters using NULL The IS NULL comparison will return records where the specified item has no data entered and has not been marked with F11 or F12 parem Joea Pirono m IS NOT KNOWS IS MISSING IS NOT MISSING The IS NOT NULL comparison will return records where the specified item does have data entered or has been marked with F11 or F12 InfoFlex QDM User Guide Chameleon Information Management Services Ltd 4 7 5 Filters using KNOWN The IS NOT KNOWN comparison returns records where the specified item has had F11 entered F11 puts a green highlight on the item in data entry and displays 88888 in Data Analysis P Hosta SAT k P NOT LIKE IS MULL IS NOT MULL IS ENDOW N IS NOT KNOW IS MISSING Yi IS NOT MISSING SQL Results 3 rows Hospital Number Hospital Name Rose Parnsien Bartley 00000 Unknown 0000 Unknown 0000 Unknown The IS KNOWN comparison returns records where the specified item has data entered or has been marked with F12 but has not been marked with F11 Data ltem Operator P Hospital Name IS KNOWN 901234 EBEGEG 123456 222222 333333 234567 456709 4 7 6 Filters using MISSING SQL Results 11 rowfs Miles Smith Green Browr Joseph Jones Gate CD300 BC20 AB100 39995 OE 400 BLAU 39995 Hospital Number Hospital N ame 5t Luke s Hospital St Mark s Hospita
28. CIMS Chameleon Information Management Services Limited INFOFLEX v5 QUERY DESIGN MANAGER USER GUIDE Chameleon Information Management Services Ltd 2015 All rights reserved No reproduction copy or transmission of this publication or any part of or excerpt therefrom may be made in any form or by any means including but not limited to photocopying recording storing in any medium or retrieval system by electronic means whether or not incidentally to some other use of this publication or transiently without the written permission of Chameleon Information Management Services Limited or in accordance with the provisions of the Copyright Designs and Patents Act 1994 as amended Any person who does an unauthorised act in relation to this copyright work may be liable to criminal prosecution and or civil claims for damages Document control Chameleon Information Management Services Ltd InfoFlex QDM User Guide Document history ts me Sencar August 2012 ae 50 0200 JW Updates for 5 50 0200 Updates to operators list 4 2 2 and filtering with memo items 4 7 7 Launch QDM from toolbar 2 1 2 index column in view Default setting of Outer Join property 5 2 5 7 4 5 3 prompt to save before running a query 2014 60 0100 JW Updates for 5 60 0100 New Median function in query view Information functions as default values for prompts banal 2015 all Update to 4 2 2 to clarify lt gt and Not IN operators don t return null values
29. DM User Guide 3 DEFINING VIEWS 3 1 Creating a view When defining views they must be created within a query group You must create them in one of the default query groups if you are in the domain the Subject Search Query Group or the Document Query Group if you are in the data view the Global query group or your username query group You can create views at the top level within a default query group however it is recommended that you first define your own structure of query groups within the default query groups See section 1 5 Defining query groups To define a view right click the query group Queries Views Filters you wish it to belong to and choose New i A Clinical Data view Expand Branch Expand Tree New Query Group Delete Query Group Rename A new query is displayed in the query group Queries Views Fitters It is represented by the symbol e el WE Clinical Data view Type a name for your view It is displayed next to the symbol and the name is also displayed in the titlebar of the main window You are now ready to add some items to your view Queries Views Filters Query Yiew Properties Referral view eae e Option 7 Clinical Data view July 2015 Page 20 Chameleon Information Management Services Ltd 3 2 Adding items to the view InfoFlex QDM User Guide The Query View Properties section of the window displays the items that are defined in the view To ad
30. DSI PROS iS oat irccunstos a a saan dtareuh slog a a tices sileghaneitan 23 IE TERT CISG bers atsa eucuveinancosma recta A E A N 24 4 Defmine HEES sicasecececscesescacsicecvecceasessasvacsuesuuesesscnencstecsesnevsaseueseeeteeanse 2S AN ASRS ALLS sa E d a a taecdviacestesnsdeteaatees 25 Ae TTS HCMC EI A asics E ene esate neces sete eo een 26 Ai Addim items tothe THe asada eat a A G 26 BD OP CTALOUS osa a A tas 28 AD es SMC V AICS eeni gaea ous vena sattpes stcaueei waves E E seated 29 4 3 Filters using multiple criteria seicsssyscsstaisveaies eaheccaiehs cancnsa dia teeamenaas vateorabianteeees 30 4 4 Filters using a mixture of ANDs and ORS ccesceeeseeseeeeeeeeeeeeeeeaeaeaeeeeeees 31 AD Reorder win HETS ae a a a 32 AG Deleting items Tron Mets sosise a a aa 32 AJT JBxamples of some filtercrniteN asien A 33 ATA FES Sna N cea a a a e i tienes 33 A 2 Falters usine CONTAINS CODE aeiae e E 33 Ao EASES WS LIKE serna wise seach ecu E on E EREA 34 ard Fiers usma NULG sonra anche A 34 AT Piters sire KNOWN pieier ene TEREA OA EE RE 35 4 hO Filters asin MISSING Foie cvsessocassaccncrnsesrecatstivecusiiementbicsecwises aden S 35 Ado Falterine with Memo Wemis we ciSiiasieci siete ia a a a aait 36 AS ECE ee a e od tise clas aeidh oy activated nego E a 37 5 Delinin Aguer in OO IE Areaan a y a a anaes 38 5 2 Addins properties 10 a QuELY ssnsisisnie a a e ae 39 Ial DESCripUOM victors Bias a a S 39 Ill VEW A E E tea Moueie
31. Join To set the query to Outer Join edit the query and set the Outer Join property to Yes To set the query to Inner Join edit the query and set the Outer Join property to No Query Properties My Query Context Distinct Rowe Mo Description VIEW My vie Filter hw filter Link at Outer Jor Yes x User Defined Mo Default Outer Join setting When a query is first created if the selected query view or event view only has only one event represented the join type will default to Inner Join Otherwise Outer Join is set Note that once the join has been set it will not subsequently change automatically This is true whether the query view or event view is changed or whether the query view itself is edited July 2015 Page 56 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 7 4 2 Exercise This exercise recreates the example described in section 7 4above In the Clinical Data view create a new filter in the Training group 1 called All patients Set the criteria to Hospital Number IS NOT EMPTY In the Clinical Data view create a new query in the Training group 1 called Join Example Select the Referral View view and the All patients filter Save the query with Outer Join set to Yes Run the query 20 records are returned Order the records by Appointment Date and note that some of the records have no Appointment Date Since Appointment Date is the event identifier of the Referral e
32. LINE TEST PATIENT 0170172002 0170172002 0170172001 O Type 1 LINK TEST PATIENT 0170172000 0101 2001 01 01 2000 0 TypeA l LINK TES T EALEL 0170172000 0170172002 01 01 2000 0 Typed The coloured dots LINKE TEST Referral A 0170172000 0170172001 1 01 2001 O Type A _ correspond to the LINK TEST PATIENT 0101 2000 01 01 2002 Lae as LINK TEST PATENT MAPT O harm TATA T Tye A alee oe a LINK TEST Referral B 01 01 2001 01 01 2002 01 01 2000 0 Type a LINK TEST 01701 2001 01701 2001 01701 2001 O Type A 1 below UNKTESI BAUENT DW0We00L _ MOLE LL MALUL L De A _ dL _ Each ay PE LINK TEST PATIENT 01701 2002 01 01 2001 01 01 2000 0 Type A EE LINK TEST Referral 01 01 2002 01 01 2002 01401 2000 0 Type A n LINK TEST 01701 2002 0101 2001 01201 2001 O Type A I l L7 Patient Registration L Referral A L Admission Date 01 01 2000 7 Radiotherapy Type Type L Referral B L Admission Date 01 01 2001 7 Radiotherapy J Type Type The coloured arrows mark the four ways in which the filter criteria are met across the subject 7 Admission Date 01 01 2002 7 Radiotherapy Type Type B 7 Referral C July 2015 Page 50 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 7 2 1 How to set the Link Level To set the Link Level edit the query and press the button in the Link at property Double click the event which you wish to set as the Common Parent
33. Mark s Hospital 2 Dentist St Luke s Hospital U GP St John s Hospital 39999 Missing St John s Hospital 1 A amp E St John s Hospital 3 General Surgeon July 2015 Page 60 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 8 2 Exercise In the Clinical Data View in Training group 1 create a view called Occurrence counting Add the Source of Referral Referral event Details panel and set the format to Code and Meaning and tick the Group By column In the second row of the view add the Count operator from the right click menu Save the view In Training group 1 create a query called Occurrence Counting Select the Occurrence Counting view and the All patients filter Run the query These results should be returned SQL Results 6 rows source of Referral 99995 Missing U GP 1 ARE 4 2 Dentist 2 3 General Surgeon 3 4 General Physician Z Add the Category item Referral event Details panel to the Occurrence Counting view Set the format to Code and Meaning and tick the Group By column Move Category to be the second row in the view Save the view Rerun the Occurrence Counting query These results should be returned SoL Results 10 rowfs 99995 Missing 99995 Missing GP ARE ARE Dentist Dentist General Surgeon General Surgeon General Physician Private MHS Private Overseas visitor HHS Overseas visit
34. RBES GATE GRIFFITHS GREEN HUGHES JOHNSTON JONES JOSEPH The Trim functions remove spaces trim to the left Ltrim or to the right Rtrim of the text In this example there are spaces leading and trailing some of the surnames These results show how the surname is returned before the trim functions are applied SQL Results 19 rows Hospital Number Smith Green Brow Jones Joseph Roze Davies Gate Forbes Clarke Smith Mourit Griffiths Parisien Bartley Hughes Johnston Miles Bates July 2015 Smith Green Brow Jones Joseph Rose Davies Gate Forbes Clarke Smith MHourit Griffiths Parisien Bartley Hughes Johnston Miles Bates Page 66 Chameleon Information Management Services Ltd InfoFlex QDM User Guide This view removes leading spaces Ltrim from the surnames in the first column and removes trailing spaces Rtrim from the surnames in the second column Query Yiew Properties My view L b Hospital Humber L E Ltrim Sumame L L 4 Atrim Sumame L L Alias These results are returned SOL Results 19 row s Hospital Number 111111 Smith Smith 123455 Green Green eee eee Browr Brown 234567 Jones Jones 3343 Joseph Joseph 345678 Roze Rose 444444 Davies Davies 456789 Gate Gate AAA AA Forbes Forbes 567890 Clarke Clarke GEEEEE Smith Smith 6rog0 Mount Mount Griffiths Griffiths Parisien Parisien or bo43 Bartley Bartley Siaja tatata Hughes Hughes 090123 John
35. This document can also be used as a training guide in conjunction with the CIMS General training database Wherever appropriate exercises are included at the end of a section The exercises in this document use the CIMS General training database InfoFles Database cms General training database CIMS Data Import training database CIMS Design Export training databaze Username EIMS Design Import training database OMS General trami sry eee Eagna CIMS Studies training databice CIMS Work List and Pathway Viewer training The Username is training and the Password is training Before starting the exercises you should login to the CIMS General training database go to Design Management and ensure that the following domains and data views are unarchived Clinical Domain Clinical Data view Training Domain Training Data view This document assumes that the user is familiar with InfoFlex Design Management and 2 July 2015 Page 5 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 1 ABOUT QUERY DESIGNMANAGER 1 1 About Query Design Manager Query Design Manager is the InfoFlex tool that allows the user to define views filters and queries and to run queries to extract data A View defines which items of data will be returned A Filter defines which subset of patients or records you wish to view the data for A Query links a view and a filter together and sets certain
36. When you need to add the subfilter switch the Items tree to display Filters rather than Items Queries Views Filters 12350001 Smith iv Items guerjes atiert D emographics Registration July 2015 Page 91 Chameleon Information Management Services Ltd InfoFlex QDM User Guide Filters are now available for selection To add a filter to the filter simply double click the filter you wish to add Queries Views Filters Query Filter Properties My filter ce ip ri Data VIEW Dataltem OOOO Data ltem Value Consultant 21230001 Smith AND hae Date prompt filter fa Filter 1 fa Filter 2 om Filter 3 oe AP hy filter i a subfilter to your filter tre Date pregnpt filter F ao Filter 1 i me Filter 2 Filter 3 eae My filter 12350001 Smith Subfilter within the filter July 2015 Page 92 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 10 6 Using Subqueries in Filters Filters can contain subqueries For example a subquery might return a list of hospital numbers and the filter criteria specify that Hospital Number is in or not in the list of hospital numbers in addition to other filter criteria ie Hospital Number IN Subquery where Subquery returns a list of hospital numbers Where subqueries are used in filters the view in the subquery must contain only one item and the filter criteri
37. a data view before defining it Note that event view summaries can use both domain and data view queries Queries defined on the domain can be used in event view summaries in any data view whereas queries defined on a data view are only available for event view summaries defined within that data view The examples shown in this document use queries in data views however the functionality is the same whether queries are being defined in domains or data views July 2015 Page 6 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 1 3 Accessing QDM QDM can be accessed from the Actions menu in the Design Management and Data Analysis modules ft InfoFlex 5 Design Management Domain Clinical Domain i InfoFlex 5 Data Analysis InfoFlex Module VIEW Definition Actions Window Help InfoFle x Module View Definitions Actions Window Help Ea B o oh B 2 Create Default Data View B a yb g F A fun view Event Study Map gt Rum Reset IS Data View Designs Find Definitions By Name F3 9 Data Views Available Outer Join seen wf Clinical Domain iene ee ee r Find Linked viewing Definitions Ctrl F3 Ain D073 Audit Data view 2 D Clinical Data view Check External References Fl Demo Domain i Electronic Patient Recor i ve Electronic Patient Recor See Ble ta e i oe Electronic Patient Recor HOU Daily Bed Status HJ Training Domain Query Design Manager
38. a from different events In the case of Complex Queries different results can be returned depending on the way the Linking and Joining parameters are set in the query 7 2 About Linking Where filter criteria are taken from multiple events the Link Level controls the way the filter criteria are applied to the View and hence affects which set of data is returned The Link Level is the level in the design tree beneath which the filter criteria must be met The event at which the link level is set is known as the common parent The filter criteria must be met in child events of that common parent For example In this design filter criteria might be defined using items from the Admission amp Surgical event and from the Radiotherapy event The filter might be to return all Referrals where Date of Admission gt 01 01 2001 from the a a ae Walue Admission event and Radiotherapy type A Date of Admission 01 01 2001 from the Radiotherapy event AND Type of radiotherapy O Type Consider this subject overview 7 Patient Registration L7 Referral A Meets criteria only when link set 7 Admission at Patient Registration level Date 01 01 2000 7 Radiotherapy Type Type A L7 Referral B Meets criteria when link L7 Admission set at Referral level Date 01 01 2001 7 Radiotherapy Type Type A Meets criteria when link set at 7 Referral C Patient Registration level 7 Admission Date
39. a item be the same item that is used in the view of the subquery Example Query Filter Properties subquery S b Fresenting Symptoms CONTAINS CODE 3 Nausea A subquery has been defined with this filter The subquery returns three patients who meet the filter criteria SQL Results 3 rows Hospital Number 456709 od 23 da bo4 A filter is defined using the subquery p pesten oga b Hospital Number Filter criteria using the subquery This filter will find patients who are in x Subquery Hospital number is the item in the list of patients returned by the vey oe subquery and who additionally have a a ee eee Sidus performance status of Fully active O fully active Notice that filter criteria item used with the subquery is the same as the item used in the view of the subquery The query using the subfilter returns these results SQL Results 1 rows Hospital Number Presenting 5 ymptoms Performance Status Only one patient is returned since only one of the patient in the results returned by the subquery also has a Performance Status of Fully Active July 2015 Page 93 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 10 6 1 Selecting a subquery in a filter To use a query as a subquery in a filter first create the query you wish to use as the subquery Next create the filter When you need to add the subquery switch the Items tree to display Queries rather than Items Qu
40. a nd rived ERTE 94 IOF NE XEGCUSC S cnn A A 95 LO el COMPariSOmeO rane AS n a r sede aanetannaee 95 LO 72 Cale lauOnis in Tien s iiss coos cccissiecncdnemcdvdcenechesac sic endsdaeetecasaeuateadecsseniadaeeessecebecsets 95 MOF SUD MING o Ac ctcgusteets eacacusasdtiete acne nea N T eta ataase tales nate 96 OTA SU GU CRICS ean eeeepan det oes vnsrngan a a geod Shama ayeaines Cnet 96 DE SEENON data ereenn Lick Export to Microsof EXE ss seutcrcsctdtssietictsSansnndasaniiatesianniensentonasasassacebsasoutasse 97 HE2 EPON Cee al Eun AE 99 12 Moving and copying views filters and queries ssceccccssssssceee LOZ 13 Syntax Differences between SQL and ACCESS ccccsccccecceeeeeees 104 13 1 Interval arguments in date FUNCTIONS eceseeeeeeeeeeeeeeeeeeeeeeeeaeeaaeeneees 104 P32 Date Calculations sos15st5etiges dette cei deGarcandansetasanaenciaeanns caus anaes eaimaaeanocnennaacas 104 13 3 TUNG MONS im Me Ses cs ost ce sess ncaa danse E 105 DSA Views and TES ernan a E E 105 13 4 1 IFNULL IFMISSING IFUNKNOWN IFMISSINGORUNKNOWN 105 e EN eet te tee een Meera eee as prey eee aebee eres gree rer nan meragton rer Satya 105 A E cea os Soh axe a china ards E A E EE EE E EE 105 FAAC onn DISUNORE 105 S AUNTS conca onanoi EDT 105 July 2015 Page 4 Chameleon Information Management Services Ltd InfoFlex QDM User Guide About this document This document is a reference guide for the InfoFlex Query Design Manager module
41. al Instead of entering a value in the Value column tick the check box in the Prompt column Query Filter Properties Referral prompt b Date of Referral Enter Prompt A filter used in a report which run for a prompted date range might look like this The user is prompted for the start date and the end date Query Filter Properties Referral prompt a Data ltem Help Text Default Value Parameter Name Date of Referral Start date ano C E Date of Referral End date Help Text The text in the Help Text column is displayed when the query is run to indicate to the user the value that is required You can specify the text that is displayed Default value If you wish the prompted field be populated with a default value enter it in the Default Value column This value can be over written by the user when the query is run The Information functions USER COMPUTERNAME WINGROUPS and WINUSER can be used as default values Query Filter Properties Referrals by user Data ltem Operator Value Prompt Help Text Default Value Parameter Name To be dealt with By Enter Frompt i COMPUTERNAME K USER USERGROUPS WINLISER For example if USER 1s selected as the default value then when the query is run InfoFlex will still prompt the user but the default value of the current user s username will be supplied as the prompt value so that the user can just OK the prompt without changing
42. arent as the link level See section 7 2 About Linking for further details 5 2 5 Outer Join The Join controls whether or not a subject s events can be retrieved when the view contains items from several different events and not all of the events used in the view exist for that subject This is in addition to the criteria defined in the filter When Outer Join is set to Yes a subject will be returned if they satisfy the criteria of the filter and as long as at least one of the events represented in the view exists for that subject When Outer Join is set to No ie Inner Join is set a subject is only returned if they satisfy the criteria of the filter and if every event used in the view exists for that subject Note that it is the event that must exist data does not have to exist in every item used in the view as long as every event used in the view exists When a query is first created if the selected query view or event view only has one event represented the join type will default to Inner Join Otherwise Outer Join is set Note that once the join has been set it will not subsequently change automatically This is true whether the query view or event view is changed or whether the query view itself is edited See section 7 4 Joining for further details 5 2 6 Distinct Rows In some circumstances the combination of the link level and the data items being returned will cause the same record to be returned more than once in
43. as Date referral recened a 3 2 1 Re ordering items Items can be re ordered within a view by dragging and dropping Pick up the grey cell to the left of the item you wish to move and drag it up or down Drop it when the grey horizontal line reaches the correct position Query Yiew Properties My view E Hospital Number Sumame Date of Birth Date of Referral A Fi Source of Referral Code and Meaning l Consultant Code and Meaning Drag and drop bepital Name Code and Meaning July 2015 Page 22 Chameleon Information Management Services Ltd 3 3 View Item Properties 3 3 1 Formats InfoFlex QDM User Guide It is possible to specify the format of date value coded and dictionary items These formats over ride any formats that are set for the items in Design Management Formats are set on the View tab of Query Design Manager in the Format column A dropdown list is displayed where it is possible to set a format Query Yiew Properties Referral view Hospital Humber Sumame Date of Birth Appointment date ddr mmyy dd mm yppy mmddyy mmddyy Date of Referral Presenting Symptoms Initial diagnosis ddrmmyypy AR in mmddyy hhinn T 3 3 2 Options Query View Properties Referral view Hospital Humber Sumame Date of Birth Appointment date Date of Referral Presenting Symptoms Code Meaning Only Code and Meaning Initial diagn
44. be entered in the same format as in Data Entry The Value column displays the same selection box that is used in data entry for the item For Coded items the Value column displays a dropdown list For MR items the Value column displays a dropdown list and allows multi selection For Boolean items the Value column displays the true and false text For Dictionary items the Value column displays the dictionary search dialog For Date items the Value column displays the date picker and enters the date in the format set in the item definition For Text items the Value column allows free text entry and applies rules regarding case and max length that have been set The wildcard can be used For Value items the Value column displays the format and unit set for the item Male a Mots 7 Query Filter Properties Referral filter b Presenting 5 alla i ea headache Nausea vomiting weightloss depression 9 Other Query Filter Properties My filter Dataten iOpen ae Piloreriesner T E E peme P GP Code Query Filter Properties My filter b Date of Referral 2010 April 4 Su Mo Tu Wwe Th Fr Sa Vac hed ee ee ela 1112 13 14 15 R17 18 19 20 21 22 23 24 25 26 27 2 29 J0 None Today Data ltem Operator Value Sumame LIKE am b Follow up irs H we When you have created your filter save it by pressing F5 or press the Save button July 2015 Pag
45. be returned when all events represented in the view exist SOL Results 14 row s Hospital Number Date of Birth Appointment date Date of Referral Presenting Symptoms i Initial diagnosis 901234 Miles 01 4071980 12 07 2000 08 01 2000 O pain 1 bleeding J033 Acute tonsillitis fod 2 Parisien O1 01 1925 04 04 2000 02 04 2000 99 Other J359 Chronic disease of tonsils and adenoids Ab Sd Clarke 0170171960 05 06 2000 DS 032000 6 depression F204 Post schizophrenic depression tape a Johnston 010717935 0670672000 0170672000 3 nausea 4 vomiting 99 Other C329 Malignant neoplasm of laryris 234567 Jones 01701 1965 017072000 30705 2000 5 weightloss DOSS Carcinoma in situ of thyroid and other end 456789 Late 01701 1955 06 07 2000 OOF 2000 0 pain 3 nausea J S Acute upper respiratory infection 345678 Roze 01701 1925 1570772000 10707 2000 1 bleeding 0 pain C322 Malignant neoplasm of subglottis 367654 Bates 01701 1930 3070772000 20r 072000 3 nausea 5 weightloss R590 Localized enlarged lymph nodes 123456 Green 07 07 7965 31707 2000 rfOr 2000 1 bleeding 4 vomiting 6 depression C099 Malignant neoplasm of tonsil unspecified 6rag0 Mount 0170171945 02 08 2000 U2 08 2000 2 headache S271 Traumatic haemothorax BREBEB Smith 0170171940 2070172003 20 12 2002 fees Brown 0170171945 2070172003 939999 333333 Joseph 0170171985 2070172003 99999 Bf 6543 Bartley 0170171970 04 08 2009 99999 7 4 1 How to set the
46. d Date of Referral These results are returned set Outer Join to No in the query definition SOL Results 15 rows Hospital Number IFMISSINGORUNKNOWN D ate fReferral valueNot4vailab Date of Referral 111111 Smith Value not available A000A fees Brown Value not available 99999 234567 Jones 3070572000 30705 2000 CEEEEE Joseph Value not available 99999 345675 Roze 1070772000 107 072000 456789 Gate 0170772000 01072000 BARA Forbes Value not available 99999 567890 Clarke 0570372000 05 03 2000 July 2015 Page 73 InfoFlex QDM User Guide Chameleon Information Management Services Ltd Example 3 This example uses IIF to display yes if the Age gt 50 and no if it isn t ol x ey IIF Referral Age gt 50 yes no a O Referral age O E O So Cl imi O yes O LJ no O O 4 These results are returned Hospital Number Sumame Age UFlAge gt 50 yes rno SOL Results 20 rowfs Hospital Number FLAage gt 50 yes no 345675 Roze E Wes foo z Farisen 75 yes 9 Be Bates flo yes OST 2 4 Johnston 65 yes DEEBGE Smith B3 peg EE Brown Ho WES 6rag0 Mount AR WES 456 89 Gate 46 no peragi Clarke 40 no of bods Bartley 40 no 1235455 Green 36 no 234567 Jones 35 no 301234 Miles 21 no 301234 Miles 20 no sas Joseph 18 na 111111 Smith n CARRER Davies na July 2015 Page 74 Chameleon Information Management Services Ltd InfoFlex QDM User Guide Example 4 Th
47. d items to a view ensure that the correct view is selected then open the Items tree to find the items that you wish to add Note that when working in a domain the Items tree displays all the events and items in the domain When working in a data view the Items tree displays the events panels and items that exist in the selected data view Items tree in h the domain Sage E Date of Therapy aoe Te Drug given al Other drug please specify iak 1 Doze of other drug given l ete Tay Frequency of other drug give Drug therapy given l i a Comments aa Tay Complications l siti 1 Nest appointment irt aan fF Date medication Prescribed men Ta Medication Type pete al Drug name pele 1 Drug dosage 1 eas Te Drug frequency 1 pii a4 Drug name 2 i TTET adh Tie aan ma You can add items either by ie ips row Data VIEW dragging them from the e Items tree into the oe a Query View Properties box or by e double clicking the item in the Items tree Notice that items that have already been selected are displayed in red in the Items tree July 2015 xl oem FEE Appointment date beets E Date of Reterral 3 cee 1 Age all Hospital Name all Consultant lf Source of Referral een T Category aa v E Date referral received 3 si EJ 1st available appointment ile 13 Days from referral to appointment oo Image item n Assessment details Surger
48. d the Symptoms filter Run the query and ensure the records returned meet the filter criteria 6 In the Symptoms filter change IN to NOT IN and then run the Symptoms query Ensure that the patients returned do not have symptoms of 3 nausea and are Fully active July 2015 Page 96 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 11 EXPORTING DATA Data can be exported to a file or to Microsoft Excel directly from QDM 11 1 Export to Microsoft Excel To export your query results to MS Excel select your query on the queries tab in QDM If you wish run a preview of it Queries VEINS Filters Queries I Query Properties My Query ii Property I Clinical Data view Description WE biy view Filter biy filter Link at Outer Jorn Mo Distinct Rows res Context User Defined Wo SQL Results 17 rows Consultant 111111 Smith 0 01 1950 19 04 2010 111111 Smith 0 01 1950 20 03 2010 20 04 2010 123456 Green 01 01 1965 27 07 2000 31 0 2000 21230004 Bi Go to the File menu and choose Export then Excel Query Design Manager Query Definition Fie Edit view Options S Quer Properties My Query Property Description WEE Wip view Filter hw filter Lirik at Outer Join Ho Distinct Rows Yes Context User Defined No July 2015 Page 97 Chameleon Information Management Services Ltd InfoFlex QDM User Guide InfoFlex opens MS Excel and displays the query res
49. e This exercise recreates the example described in section 7 3above Edit the Link example view created in the Linking exercise in 7 2 2above Delete the last 3 items in the view so that the view only contains Hospital Number and Appointment Date Save the view Run the Link Example query with the link level set at the Patient Registration event 12 records are returned but since the items from the child events are not present there appear to be duplicate records Set Distinct Rows to Yes Save then rerun the query The duplicate rows are removed and now only 3 records are returned Chameleon Information Management Services Ltd 7 4 Joining InfoFlex QDM User Guide Joining affects which set of data is returned when multiple events are represented in a view This is in addition to the criteria defined in the filter Joining controls whether or not a record can be retrieved where not all of the events used in the view exist for that subject When Outer Join is set to Yes a subject will be returned as long as they satisfy the filter criteria and as long as at least one event used in the view exists for that subject When Outer Join is set to No ie Inner Join is set a subject is only returned if every event represented in the view exists for that subject Note that it is the event that must exist data does not have to exist in every item used in the view as long as every event used in the view exists If there is no data in an
50. e 29 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 4 3 Filters using multiple criteria If you wish to define a filter that uses more than one criterion the criteria are linked together using operators The AND operator returns records that fulfil both criteria The OR operator returns records that fulfil either of the criteria For example This filter would return only those patients who were both male and single ie each patient has to meet both criteria in order to be returned This filter would return all those patients who are male and would also return all those patients who are single ie each patient has to meet only one of the criteria in order to be returned Data ltem Operator Value See 0 Male OA Marital Status 1 Single To add an AND or an OR right click the row beneath which the AND or OR should appear and choose Add Operator then the required operator Query Filter Properties Referral filter H Data ltem Value 0 Male b Ser Delete 4dd Operator 4dd Prompt Plus Minus Multiply Divide Open Bracket Close Bracket Value Expr Row The same item can be used more than once in a filter 1f necessary July 2015 Page 30 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 4 4 Filters using a mixture of ANDs and ORs Where a mixture of ANDs and ORs are used in a filter brackets can be used to define how the crit
51. e Date of Referral and a calculation of Now Date of referral 3 Create a query in Training group 2 using the above view and filter and check that the correct results are returned Create a filter in Training group 2 which finds records where the difference between Date of Referral and Appointment Date is greater than 2 i Create a filter with the calculation DateDiff d Date of Referral Appointment Date gt 2 See section 10 2 1 2 For testing create a view in Training group 2 which contains the Date of Referral Appointment Date and the DateDiff calculation used in the filter 3 Create a query in Training group 2 using the above view and filter and check that the correct results are returned Create a filter in Training group 2 which finds records where the difference between Date of Referral and Appointment Date is greater than a prompted value l Edit the filter you created in the previous exercise so that the user is prompted for the greater than value See section 10 2 2 2 Run the query you created in the previous exercise and check that the correct results are returned July 2015 Page 95 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 10 7 3 Subfilters This exercise creates a filter that prompts for the earliest and latest Date of Referral and the Consultant Since we have already created a filter that prompts for the earliest and latest Date of Referral we shall use that filter as a
52. e following example uses HF displays yes if the Date of Referral is null and to display no if the Date of Referral is not null Note that different syntax is required for Access and SQLServer for ascertaining if the Date of Referral is null The syntax for Access is as follows Query Yiew Properties My view Ceea Daattens Hospital Humber Sumame Date of Referal Appointment date IFISNULLID ate of Referral pes no The above formula was created by adding the Date of Referral item to the view below the Appointment date item then opening the formula builder and building the formula as shown below 10 x IIF ISNULL Referral Date of Refterral ea r Hg rr l IIF O eer E ISNULL L Referral Date of Referral P go C Tres E LC ryg o O These results are returned SQL Fesults 15 Hospital Number Date of Reterral HF UISMULL D ate tReterral ves no 111111 00000 197042010 GEGEEE Smith 20 01 2005 Ves 3456 5 Rose 10707 2000 15707 2000 no rod 2 Parisien 02 04 2000 04 04 2000 no 67090 Mount 02708 2000 yes 901234 Miles 0870172000 1270172000 ne S53353 Joseph 39939 2070172003 ne July 2015 Page 75 Chameleon Information Management Services Ltd InfoFlex QDM User Guide The syntax for SQLServer is as follows Function Data ltem Hospital Number Sumame FIFA ULL Surname no data no data pes rnol Date of Birth HFRUFNULLID ate of Birth
53. e group Fl 9 Clinical Data view to which you have moved or copied it El Analysis Group WEE a Join a My view Wiew Note that when views filters or queries View 2 are copied the name of the new view S View 3 filter or query has a number eg 1 a yell appended to it so that the samenameis ul a Training queries not used twice T July 2015 Page 103 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 13 SYNTAX DIFFERENCES BETWEEN SQL AND ACCESS When functions are used in Views and Filters the expressions are converted into SQL expressions and then SQL Server or Access evaluates the expressions rather than InfoFlex In some cases views and filters need to be defined differently for Access Some examples are listed below Note that if a database is moved between platforms eg an SQL database is converted into an Access database then any existing query syntax that is specific to the database platform will not be converted Some queries might therefore fail because the syntax is not appropriate for the new platform 13 1 Interval arguments in date functions a Access requires interval arguments to be in double quotes These are inserted automatically by the formula builder when the database is Access If the database is SQLServer and quotes are placed around the interval arguments InfoFlex automatically removes the quotes b SQLServer and Access use slightly different int
54. ecsssssseseeseeeeeeeeeeeees 78 Da simple CaleulatOns 1a Vie WSs a a e A a N 79 9 7 1 How to define calculations in VICWS sssereessssseeeeesssssrceressssseresesesereressssseeres 79 9 8 Summary of Operators that can be added to VICWS cccccceceeeeeeeeeeeeeeeeeees 81 99 TEXT CUS S cis ta pcs Sawasdee ass aan saben T 82 9 9 1 Functions and multiple functions eseeessssssssoeeeeeressssssssssseseerreessssssssssseeee 82 9 9 2 Aggregated values grouped by patient ccceccccccccccccceceeeseeeseeeeeeeeeeeeeees 82 DPSS ACOMCALCN AMON nao ina E S E EE RE AEE E EEES 83 OO FRECV eaa r a a r a ween es eengees 83 995 Expressions 1 Vie WS enccicni snin a a veces 83 D596 Simple calculations sees e a A A 83 Comparisons and Calculations in Filters cccccccccssssssssssssssssees OF IOI omparisOnmon PiClGS sx ico 6h cse cnt sh cc aa a a E a neti E 84 10 2 Calculations aml iters siini uncon E A ia eaeaan means 85 LOD A gt Addins TUN MOMS to AL CES aisse E E ATER EE 86 22 PAG PrO aea a a E Dene AN 86 10 3 Calculations in Filters using Fields containing Blanks nsnn00essseeeeeeeeen 88 10 4 Summary of Operators that can be added to filters ceeeeeeeeeeees 90 105 Usin Subite i FE S naon aa 91 106 Using Subguernesin Filters iransi aa a e a ia Raa 93 InfoFlex QDM User Guide Page 3 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 106 1 Selecting a subquery ma filter srira
55. eferral Code and Meaning LI b Counti 7 Function Dataltems Format Options Sot Group By Hide Alias i July 2015 Page 59 Chameleon Information Management Services Ltd InfoFlex QDM User Guide When this view is run in a query the results show the number of times each value entered in the Source of Referral item has been used SQL Results 6 rows source of Referal e000 Unknown 1 99995 Missing 2 0 GF 6 1 A amp E 4 Dentist 3 General Surgeon 3 This function can be used with coded boolean dictionary date text and value items coL Results 6 row s Hospital Mame Sooo Unknown 999959 Missing AB100 St Matthew s Hospital BC200 St Mark s Hospital CE300 St Luke s Hospital DE400 St John s Hospital This function can also be used with more than one item to show the number of times a combination of values occurs Each item must have the Group By option ticked Query View Properties Source of referral Count oe en _ on eee Hide Alias a Hospital Name Code and Meaning L Source of Referral Code and Meaning E ia Lourt L LI The results show the number of times each combination of values has been entered SQL Results 10 rows Hospital Name Source of Referral Unknown A amp E 5t Matthew s Hospital O GP St Matthew s Hospital 3 General Surgeon St Mark s Hospital U GP St Mark s Hospital 1 A amp E St
56. em does not contain one All types except memo of the codes entered in the Value column Note that null values will not be returned when using this operator eg a filter of Dataltem NOT IN 1 2 will only return records with values entered and will not return records where Dataltem has no value entered An additional criteria of OR Dataltem IS EMPTY is required return records where the specified item contains the All types including memo text entered in the value column The wildcard can be used contain the text entered in the value column entered and has not been marked with F11 or F12 data entered or has been marked with F11 or F12 Behaves the same as IS EMPTY If you select IS All types except memo NULL the operator changes to IS EMPTY on saving Behaves the same as IS NOT EMPTY If you All types except memo select IS NOT NULL the operator changes to IS NOT EMPTY on saving returns records where the specified item has data All types except memo entered or has been marked with F12 but has not been marked with F11 returns records where the specified item has had All types except memo F11 entered data entered or has been marked with F11 returns records where the item contains the code Multiple Response only Specii imne Valoaga aae Maipapan Page 28 Chameleon Information Management Services Ltd 4 2 3 Entering values InfoFlex QDM User Guide When entering values the value needs to
57. erator Column and enter 30 in the Value column July 2015 Page 85 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 10 2 1 Adding functions to filters The operators described above can be used to add the syntax of a function to a filter The following example returns records where the difference between Date of Referral and Appointment Date is greater than 2 Query Filter Properties My filter b DATEDIFF d eS Date of Referral The above filter was created using the following sequence e Right click and select Add Operator and then select Value e Add DATEDIFF d to the Value column of the blank row e Double click the Date of Referral item and remove the operator e Right click and choose Add Operator then Value e Type acomma in the Value column of the blank row e Double click the Appointment Date item and remove the operator e Right click and select Add Operator then Close Bracket e Right click and select Add Operator then Value e Enter gt 2 in the Value column of the blank row 10 2 2 Add Prompt When defining a calculation or function in a filter as described above you can prompt for some of the elements instead of including them in the view To add a prompt for part of the calculation or function right click in the filter and choose Add Prompt and then one of the available options Query Filter Properties My filter w z DATEDIFF d E Date of Aeteral Appoin
58. eria are applied Itis possible for the same criteria to return different results with a different arrangement of brackets This filter returns this set of patients Query Filter Properties Referral filter Data ltem Value Cat Female 1 Female ow ay ar wo a S ll ll 1 Single 3 Divorced i Marital Status Whereas this filter returns this set of patients Query Filter Properties Referral filter Sex Female a Data ltem Value b Sex 1 Female Marital Status 1 Single Status Divorced Marital Status 3 Divorced If no brackets were used then the AND is applied first as in the first example This filter applies the AND first and returns this set of patients Query Filter Properties Heferral filter basen e b Ser 1 Female JAND Marital Status 1 Single 3 Divorced July 2015 Page 31 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 4 5 Re ordering within filters When adding items and operators to filters you can re order them any time by dragging and dropping Drag the grey square to the left of any item or operator and move it up or down in the grid Drop the item or operator when the grey line is in the correct position AND Al 7 A ln Mafital Status 2 2 Maried Se OF Marital Status 3 Divorced 4 6 Deleting items from filters To delete any item
59. eries Views Filters Filters js I Clinical Data view E Analysis Group c E GLOBAL Global group 1 Click here to switch from Items to Queries T All i i Teme 3 view a Hli Registration es atient Demographics i h F Details a ai g Referal r ve Fy Details QUerles Queries are now available for selection To add a query to the filter simply double click the query you wish to add Queries Views Filters Double click to add a subquery to your filter July 2015 Page 94 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 10 7 Exercises 10 7 1 Comparison of fields 1 Create a filter in Training group 2 to find records where the Date of Admission Referral event Surgery details panel lt Date of Referral 2 Make a copy of Referral view and put it in Training group 2 Edit the copy of the Referral view and add the Date of admission Referral event Surgery details panel to the view 3 Create a query in Training group 2 using the copy of the Referral view and the filter you have just created Run the query to test your filter 10 7 2 Calculations in filters Create a filter which finds records where the Date of Referral is more than 30 days earlier than today s date 1 Create a filter in Training group 2 with the calculation Now Date of Referral gt 30 See section 10 2 2 For testing create a view in Training group 2 which contains th
60. erval arguments These are listed below Date Part SQL Server MS Access _ Quarter quarter aqq a Month month mm m_ m Day day ddd fo Millisecond _ millisecond ms f For example In Access DateDiff d Dateltem Now In SQL Server DateDiff day Dateltem Now or DateDiff dd Dateltem Now or DateDiff d Dateltem Now 13 2 Date calculations When subtracting one date from another SQLServer returns an error whereas Access returns a value For this reason functions eg DATEDIFF should always be used for calculations involving dates July 2015 Page 104 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 13 3 Functions in filters The syntax for substituting null with a value is different in SQLServer and Access This syntax would be used where a record has a null value in any of the items used in a filter calculation Substituting the null value with another value such as zero to ensure that a record will be returned by the query In SQLServer the syntax to use 0 instead of Null for a data item called ValueItem is as follows IsNULL Valueltem 0 In Access the syntax to use 0 instead of Null for a data item called Valueltem is as follows IIF ValueItem 0 See section 10 3 for full details 13 4 Views and filters 13 4 1 IFNULL IFMISSING IFUNKNOWN IFMISSINGORUNKNOWN IFNULL IFMISSING IFUNKNOWN IFMISSINGORUNKNOWN can be used in views but not in filters In views these
61. es Create a query within the group called Functions Create the views described below To test each view select the Functions query and replace the view with the new view you have created Run the query There is no need for a filter 1 Create a view which counts the number of Appointment Dates 2 Create a view which counts the Distinct number of Appointment Dates 3 Create a view which displays both the earliest Date of Referral and the latest Date of Referral 4 Create a view which displays the sum of the Duration of symptoms 5 Create a view which displays both the average Days from Referral to Appointment and the Standard deviation of Days from Referral to Appointment 6 Create a view which displays the Forename in lower case and the Surname in upper case 7 Create a view displays the lowest highest and average Days from Referral to Appointment 9 9 2 Aggregated values grouped by patient This exercise creates a query returning the earliest and latest Date of Radiotherapy for each patient l Create a view containing Hospital Number and Surname and two instances of Date of this radiotherapy taken from the Radiotherapy event 2 Tick the Group By column for Hospital Number and Surname 3 Use the Min function for the first instance of Date of this radiotherapy 4 Use the Max function for the second instance of Date of this radiotherapy 5 Create a new query Use the All patients filter and set Outer Join to No 6 Run the query
62. f the number of different values have been recorded in the Days from Referral to Appointment field Query Yiew Properties my view b Count Distincti Days from referral to appointment soL Results 1 rowfe Count Distinct D ays from referral to appointment One row is returned Note that Count Distinct should normally return a lower value than the Count function since Count simply returns how many records have a value entered where as Count Distinct returns the number of different values entered Count Distinct can only be used in SQLServer databases 9 1 3 Min The MinQ function finds the lowest or earliest value This example returns the earliest Date of Radiotherapy in records that meet the filter criteria Query View Properties Referral view b Hirt Date of this radiotherapy One record 1s returned SQL Results 1 rowfs Min Date of this radiotherapy 01 07 2000 9 1 4 Max The Max function finds the highest or latest value This example returns the latest Date of Referral in records that meet the filter criteria Query Yiew Properties Referral view b Wai Date of Referral One record is returned SOL Results 1 row s Max Date of Referral 20 12 2002 July 2015 Page 63 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 1 5 Sum The Sum function calculates the sum of the values that meet the filter criteria This example returns
63. from a filter right click the row and select Delete Data ltem Operator p Date of Referral Add Operator P Add Prompt July 2015 Page 32 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 4 7 Examples of some filter criteria 4 7 1 Filters using IN The IN comparison returns records where an item contains one of several values entered in the Value column This comparison saves adding multiple criteria based on the same item linked with ORs Note that when typing in multiple codes they should be separated by semi colons This filter Query Filter Properties Referral filter Daen per b Source of Referral O GP oR Source of Referral 1 ABE oF E Source of Referral 2 Dentist D ata Item 7 Uperator IM Value O GP 7 AAE 2 Dentist Can be expressed as p Source of Referral The NOT IN comparison will return records where the value stored for the coded item is not one of the codes entered in the Value column Note though that null values will not be returned when using this operator eg a filter of Dataltem NOT IN 1 2 will only return records with values entered and will not return records where Dataltem has no value entered An additional criteria of OR Dataltem IS EMPTY is required 4 7 2 Filters using CONTAINS CODE The CONTAINS CODE comparison can only be used with multiple response MR coded items It returns records where the data entered
64. functions can be used both in SQLServer and in Access 13 4 2 IsNull SQLServer supports IsNull argl arg2 in both views and filters although it is not listed in the formula builder IsNull arg1 arg2 cannot be used in Access Access supports IsNull arg1 in both views and filters IsNull arg1 cannot be used in SQLServer 13 4 3 IHF IIF cannot be used in a filter in SQLServer IIF can be used in a filter in Access 13 4 4 Count Distinct Count Distinct cannot be used in a view in Access 13 5 String concatenation Strings in SQL Server should be concatenated using the symbol whereas strings in Access should be concatenated using the amp symbol The arguments being concatenated should be strings rather than numbers Note that QDM allows you to enter the invalid syntax however the query will fail to run July 2015 Page 105
65. haasaundenss 39 32 Pe a E eb ictaetad Gena aeoe 39 ae aE E S 40 SA OWEJ O aaa T E 40 Dace DENCE ROW Ss o a aa E E 40 IT COMEX ani A E E E nn 40 SAS User denne deses 40 July 2015 Page 2 Chameleon Information Management Services Ltd July 2015 10 Sot RUDNA Yee ae ad gntes tata taetesudnnleininian Suureutintuaitad 41 Die TOUIMICS AMC GUCE Sekaa eaen e eeus ey enndadt 42 Decay SP CSS GUEh ye S UNIS aah ittc cue tsvoseets sia o i accie a ecaietanneee a eaee eens 42 Oe WAC WAN SQD ators daar E EE Sua tuoa aa Oa 43 Pe E WM GW ecstatic daann tet an de N 44 Ir PER a REn EoD eS ECR ISR een A ER OF Ene NEE en SESE nr 45 Prompt Filt rs zonnen a a a a aa 40 Gl Defining a prompt Alter arien a E 46 Running a query containing a prompt filter ossseooooeeeeessssssssssssssetereresssessssssssss 47 02 EXECS rer iiion cau neeesueu nose noua study oa eneeuinet coma uunoeennae 48 COUCEY PATAMCUCES isi cctescssisctesccecsascesneacinccnetoncensesdenccnssaseeshuvssnececteacsncn gt dk AIP SOE GUC oie eerccesrce cde ob reuse es neta vt te ermecnurac aden tee ya aade oda ne re hecmiobttcacites 49 T2 ROOM WATKAT toa utcecensieecdstte ors camo A E 49 2A How toset the Link Lev lit ceeiveiseteiaverieusieterlesvetsddaactend tneieandadtve teas 51 Dred EROIC IE cvarecnccia iasscatisnedtasine NE TA AE 52 Teor AD ASUTCUTROWS eis cdcseces ssdosns cade onesietuerscusaomdeescch E E O ES 53 Tad HOW tO Set Distinct ROWS sern a E 54 Wied E o
66. he Date of Referral filter criteria to the Bates 01 01 1930 21230014 Clark view to ensure that the results are correct 28 07 2000 July 2015 Page 42 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 5 3 3 Viewing SQL You can view the SQL that is created by your view filter or query definition File Edit yew Options mj ab Gh Queries The View menu has an SQL option which is available whether you are FF views i e e e Quenes i working in the Queries Views or Filters tab T Filters TEE a c 7 Items When this option is switched on an SQL panel is displayed at the bottom of afa the tab and the SQL represented by the current view filter or query is displayed Note that you should save your view filter or query to ensure that the SQL is up to date Queries Views Filters Query Filter Properties my filter 1 Female 1 Single 3 Divorced aB NHS Number SQL panel switched on a Sumame and off in the View menu a4 Forename to Pinks hans HIN Pin i ci Ses T AND Marital Status 1 OR Marital Status 3 J Quenes WEWE Filters Query Properties my query Property Description ipa rte Data view On the Queries tab the SQL is He View my view i t always available pau ee Queries Filter my filter when a query has m EB my query Link at been ae An Outer Join es SQL tab is Distinct Rows No d
67. he filter and type the relevant syntax in the Value column of the blank row Query Filter Properties My filter IIF an The above filter was created using the following sequence e Right click and select Add Operator and then select Value e Type IIF in the Value column of the blank row e Double click the ValueItem item and remove the operator e Right click and choose Add Operator then Value e Enter 0 in the Value column of the blank row Here is an example of the syntax in use in a filter Queries Views Filters Filters SS Query Filter Properties Adjusted Wait lt 14 days _ Data Item sens Clinical Data view EY GLOBAL en A aatem Appointment date Y Adjusted Wait lt 14 days cow SP My filter Date of Referral HF a0 July 2015 Page 89 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 10 4 Summary of Operators that can be added to filters Below is a summary of the operators that can be added to views 1 Adds a row containing Open bracket Adds a row containing Close bracket Adds a row containing Value Adds a blank row into which numbers letters or symbols can be entered in the Value column Expr Adds a blank row into which numbers or letters can be entered in the Value column An operator can be selected in the Operator column Adds the Now function The following prompt operators can be added to filters Value The use
68. he items you have added turn red in the tree and the events and panels from which items have been selected are selected are also displayed in red Queries Views Filters Filters ee Ba Domain Y Consultant prompt Y Females Y Males a4 Hospital Name a4 Consultant a Initial aP Title Caan i Category July 2015 W Referral date prompt Appointment date i Source of Referral ne BA Date of Referral EJ Date referral receiwed E t available appointment Query Filter Properties My filter Page 27 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 4 2 2 Operators Below is a list of the operators that can be used in filters and their meanings Some operators are only available for certain item types Operator lt gt A l Z NOT IN LIKE NOT LIKE IS EMPTY IS NOT EMPTY IS NULL IS NOT NULL IS KNOWN IS NOT KNOWN IS MISSING IS NOT MISSING CONTAINS CODE July 2015 Item types All types except memo Not equal to All types except memo Note that null values will not be returned when using this operator eg a filter of Dataltem lt gt 1 will only return records with values entered and will not return records where Dataltem has no value entered An additional criteria of OR DataItem IS EMPTY is required returns records where the item contains one of the values entered in the Value column returns records where the it
69. hem would not be included in the view In this case you can choose to display Distinct Rows in order to remove the apparent duplicates With the same filter but fewer items in the view the same records are displayed but now there are no items from the Admission or Radiotherapy events to distinguish one record from another Setting Distinct Rows to Yes will reduce the results to show distinct records only SQL Results 3 row s Hospital Number Appointment date LINE TEST PATIENT 01 01 42000 LINE TEST PATIENT 01 01 42001 LINK TEST PATIENT 01 01 42002 SOL Fesults 12 rows Hospital Number Appointment date LINK TEST PATIENT 0170172000 LINE TEST PATIENT 0170172000 LINEK TEST PATIENT 0170172000 LINE TEST PATIENT LINE TEST PATIENT LINK TEST PATIENT LINE TEST PATIENT LINE TEST PATIENT LINE TEST PATIENT LINE TEST PATIENT LINE TEST PATIENT LINE TEST PATIENT July 2015 01 01 2000 0170172001 0170172001 0170172001 0170172001 0170172002 0170172002 0170172002 0170172002 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 7 3 1 How to set Distinct Rows To switch on Distinct Rows edit the query and set the Distinct Rows property to Yes Query Properties link Description Whew lirik Filter lirik Link at Patient Registration Outer dorn No Distinct Rows GE Context i User Defined Mo Note that by default Distinct Rows is set to No 7 3 2 Exercis
70. ia coL Results 11 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 5 5 Exercise l In training group 1 create a query called Referral query Set Referral view as the view Set Referral filter as the filter Save and run the query Note that since Date of Referral is in the view so you can easily check that the right data is being returned the filter is Date of referral gt 1 1 2000 2 View the SQL on the Queries Views and Filters tabs 3 Make a copy of the query in the same group In the copy set the view to Count of the Referral event Save and run the query One row is returned which is the number of Referral events that meet the filter criteria July 2015 Page 45 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 6 PROMPT FILTERS 6 1 Defining a prompt filter Prompts in filters allow the user to specify filter values when the query is run rather than having to continually update the value specified in the filter itself For example if a weekly report is run the user can be prompted to enter the date range every time the query is run rather than editing the filter every week or if the same report is required for several different consultants one prompt filter can be defined and the user can be prompted for the consultant name on running the query rather than defining one query for each consultant To define a prompt filter create a filter and add an item and operator as usu
71. ichever filter is used in that query July 2015 Page 16 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 2 1 2 Reviewing view definitions When you select a view on the Views tab the name of the view is displayed in the titlebar of the main window and the items that belong to that view are displayed in a grid beneath the titlebar You should always double check that the correct name is displayed in the titlebar to ensure that you are viewing the correct view The Query View Properties grid shows all the items that exist in the currently selected view Each row in the view is numbered starting from zero This numbering will be helpful when mapping queries into reports The Items tree displays all the items in the current domain or data view Note that when working in a domain the Items tree displays all the events and items in the domain When working in a data view the Items tree displays the events panels and items that exist in the data view that you are creating the view in If you select a cell in the grid the row number and column heading of the selected cell are highlighted in blue Additionally the Items tree is expanded and the item is highlighted in the tree Notice that in the Items tree items that already belong to the view are displayed in red and events and panels from which items are selected are also displayed in red Quenes Views Filters Views very View Properties My view
72. iews that a AT Group 1 P tinct o i have been defined within each sida ai an Context a View group To select a view double coon EE view 2 User Defined l LENN click it T e View 3 E E jsmith 5 uery Properties Referral quer After selecting a view it 1s Cuei query displayed in the View field Description This query is for View My View 2 E Event Counts x 5 2 3 Filter Query Properties Referral query Description This query ig for View My View 2 Filter hy filter 2 Link at IE J Clinical Data view To select a filter use the E button to display a list of the available filters The list displays the Global group and the user s own named group Outer Join as well as any subgroups and ene Ef Group 1 the filters that have been Eanes a Bs Filters User Defined ee ismnith defined within each group To select a filter double click it After double clicking a filter it is displayed in the Filter field Description This query is for View My View 2 Filter My filter 1 Fe July 2015 Page 39 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 5 2 4 Link at The link level is used where the filter contains criteria from multiple events The link level is the level in the design tree beneath which the filter criteria must be met By default InfoFlex sets the lowest possible common p
73. in the MR coded item includes the code specified in the Value column The MR item may contain other codes as well as the one specified returns all records where 3 nausea This filter exists in the Presenting Symptoms item Presenting Symptoms OU pain 3 nausea b Fresenting Symptoms CONTAINS CODE 3 nausea 3 nausea 4 vomiting 33 Uther 3 nausea 5 weightloss CONTAINS CODE can be used with AND OR and NOT to define the filter further however brackets are required around the separate CONTAINS CODE clauses eg Query Filter Properties Contains Data ltem Operator Value bi Presenting Symptoms CONTAINS CODE pain AND NOT 7 Presenting Symptoms CONTAINS CODE 3 nauzea The example above will include all patients whose Presenting Symptoms item contains 0 pain but does not contain 3 nausea eg It would include 031 and 0 but not 03153 July 2015 Page 33 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 4 7 3 Filters using LIKE The LUKE comparison returns records where the specified item contains the text entered in the value column This comparison can be used with text items or to search dictionary codes The wildcard can be used This filter returns all records where the surname begins with J Surrarnne b Sumame LIKE J The NOT LIKE comparison will return records where the specified item does not contain the text entered in the
74. isplayed next to Context the Results tab User Defined No Hell i Results 5 rows SELECT Hospital Number Surmame Forename FROM lfs 20E1D ata LEFT JOIN Iski 20E vents 45 e1 ON lfM20E 1D ata RecordNumber e1 AecordHumber WHERE Sex T AND Marital Status 1 OR Marital Status 3 J July 2015 Page 43 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 5 4 Event counting When defining a query instead of selecting a view you can choose to count the number of events that meet the filter criteria This is done by using the Event Counts function Instead of returning data items the query will simply count how many occurrences of the selected event match the filter criteria In the View selection box press the Event Counts button instead of using the dropdown list of views The event tree is displayed and you may select any event Query Properties My Query Description View Filter Link at Outer Jor Distinct Rows Contest User Detined On saving the View property displays Count of followed by the name of the event selected Also on saving Distinct Rows is automatically set to Yes Query Properties My Query Property Description View Count of Referral Filter hay filter Lirik at Outer don Mo Distinct Rows Yes Context User Defined Mo When this query is run one value is returned which is the number of events that match the filter criter
75. items that exist in the data view that you are creating the view in If you select an item in the grid the Items tree will be expanded and the item will be highlighted in the tree Notice that in the Items tree items that already belong to the filter are displayed in red and events and panels from which items have been selected are also displayed in red Y Query Design Manager Filter Definition File Edit wiew Options E ela A Y Queries Views Filters i t Gs Pilte y Filter Properties pi filter Date si a gt 01 01 2010 O j Saki LI gt Consultant code Enter Prompt Select an item in the grid and it will be highlighted in the Items tree is re Details pais EJ Appointment date t Ta Category cae E Date of Referral ee ad Hospital Name aa at Consultant code aiai Ta Source of Referral July 2015 Page 18 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 2 2 Exercise In QDM for the Clinical data view expand the Queries heading attached to the training query group and select My query Review the query definition Go to the Views tab and note the My view is selected since it is the view used in the selected query Review the view definition Go to the Filters tab and note the My filter is selected since it is the filter used in the selected query Review the filter definition July 2015 Page 19 Chameleon Information Management Services Ltd InfoFlex Q
76. l St Matthew s Hospital Missing St John s Hospital St Mark s Hospital Missing The IS MISSING comparison returns records where the specified item has had F12 entered F12 puts a blue highlight on the item in data entry and displays 99999 in Data Analysis baaten O O b Hospital Hame IS MISSING eeeeee 456709 Brown Gate Johnston SQL Results 3 rowfs Hospital Number Sumame Hospital Name 99995 Missing 99995 Missing 99995 Missing The IS NOT MISSING comparison will return records where the specified item has data entered or has been marked with F11 but has not been marked with F12 IS NOT MISSING b Hospital Name 901 234 BEEBE 123456 Jdddda 234567 345675 6ra 6rog0 food 2 SQL Results 11 rows Miles Smith Green Joseph Jones Roze Clarke Mount Parisien CO 300 BC 200 ABOU DE 400 BC2O0 D0000 CD S00 DE 400 D0000 Hospital Number Hospital Name St Luke s Hospital St Mark s Hospital St Matthew s Hospital St John s Hospital St Mark s Hospital Unknow St Luke s Hospital St John s Hospital Unknow July 2015 Page 35 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 4 7 7 Filtering with memo items Only the LIKE NOT LIKE IS EMPTY and IS NOT EMPTY operators can be used with memo items To retrieve memo items that are either empty or null the following syntax should be used
77. l Data view ae qo Patient Registration Fp Eb A q Patient Registration A pe Referral Ww ren A aa nels y Surgical Event r Pn EN ee jol xj ications Surgical on Desi Manaa 1e Definition File Edit View Options py Event r a selamy E Quernes Views Filters py r ue Query Properties py Ut si a a H QDM is displaying Clinical data os view queries because the Clinical data view is currently selected When opening QDM from the Data Analysis module QDM displays the data view which is currently selected for analysis f InfoFlex 5 Data Analysis InfoFlex Module View Definitions Actions Add Ins Window Help elg talk oboe l aly SBlleg File Edit wiew Options Data Yiews Available Clinical Data view FS Event Views s H apelam amp Queries Views Filters Que es ee li f Query Properties Property Description f InfoFlex 5 Data Analysis InfoFlex Module View Definitions Actions Add Ins Window Help File Edit view Options E B t e laby a j MN ueries Views Filters Query Properties Property Description July 2015 Page 8 Chameleon Information Management Services Ltd 1 4 Query Groups InfoFlex QDM User Guide Query Groups allow the grouping of Views Filters and Queries to enable them to be managed more easily if a large number have been created Query groups behave
78. like folders or directories Some default query groups are defined automatically Additional query groups can be defined within the default query groups The symbol indicates a query group The symbol indicates a query 1 4 1 Query groups in a domain Within a Domain two query groups are created by default for Subject Search queries and for Document queries Queries can be copied and pasted between these two groups however the two groups have their own purposes Queries should therefore be defined within the appropriate group or they may not be available for selection Subject Search queries are for use in Data Entry They are defined in QDM then selected in a data view definition in Design Management When selecting subject search queries within a data view definition only queries within the Subject Search group are available for selection Document queries are for use in document and report definitions When selecting queries within a document or report definition only queries within the Document group are available for selection Query subgroups can be created within each of the above groups to enable management of the queries It is recommended that an appropriate structure of query groups and a naming convention for queries and query groups are used July 2015 ps Clinical Domain Subject Search Query Group o fe uenes ne Quent EA Fs Querz Paii Fs Quera _ Querytl ee Fe Query05 er Aon Q
79. n a query using the All patients filter July 2015 Page 83 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 10 COMPARISONS AND CALCULATIONS IN FILTERS 10 1 Comparison of Fields Filters can be used to compare one field against another and thus return records where for example one date is earlier than another or one date or one field equals another field This example returns records where the Date of Admission is earlier than the Date of Referral Query Filter Properties Comparison b Date of 4dmission Date of Referral Note that by default when you add an item to a filter the operator is always added To create the above filter you will therefore need to remove the operator from the Date of Admission item Query Filter Properties Comparison Data tern opaan Wh ting this filt Date of Admission a eee ee delete the operator b Date of Referral This example returns fields where Date of Admission is the same as Date of Discharge Query Filter Properties My filter b Date of Admission A Date of Discharge from Hospital Chameleon Information Management Services Ltd InfoFlex QDM User Guide 10 2 Calculations in Filters Calculations can be carried out in filters Plus minus multiply divide brackets and the NOW function can all be used in combination with data items The Value and Expression operators can also be used to add values and expressio
80. n the filter even if those items are not needed in the final version of the query For example your query might be returning a list of patients who were referred within a certain time period Query Filter Properties Filter 1 P Date of Referal 01201 2000 3171272000 The report may not need to display the Date of Referral for each patient but while you are testing your query it is useful to add the Date of Referral to the view so that you can ensure the results are correct Once the query is producing the correct results you can remove the Date of Referral from the view Initial diagnosis Date of Referral Miles 0170171 21230002 Jones pain 1 bleeding J039 Acute tonsillitis Green 01 01 1965 21230004 Brown Ser Physician 1 Bleeding 4 vomiting 6 depression C099 Malignant neoplasm of tonsil unspecified Jones 01 01 1965 21230002 Jones A amp E weightloss D093 Carcinoma in situ of thyroid and other endocrine glans Rose 01 01 1925 21230011 Jones Dentist bleeding 0 pain C322 Malignant neoplasm of subglottis Gate 01 01 1955 21230009 Hughes 3 General Surgeon pain 3 nausea JOES Acute upper respiratory infection Clarke 01 01 1960 21230016 Evans General Physician 6 depression F204 Post schizophrenic depression Mount 01 01 1945 21230013 Brown headache 5271 Traumatic haemothorax Parisien 0120171925 2123001 Green l Johnston 01 01 1935 Z1230016 Evans 1 Add t
81. ns into the calculation Note that the Expr operator allows an operator to be entered in the Operator column whereas the Value operator does not Both Value and Expr allow you to type free text in the Value column Note that it can be helpful to display the SQL on the Filters tab see section 5 3 3 whilst creating calculations in order to be sure that the syntax and location of the commas in functions etc is correct To add any of the above operators to a filter right click in the filter and choose the operator you require Query Filter Properties My filter Delete 4dd Operator b nd Add Prompt Or Mot Flus Minus Multiply Divide Close Bracket value Expr Pow The following example returns records where Date of Referral is more than 30 days earlier than today s date Queries Wiews Filters se Clinical Domain Clinical Domain Document Query Group W Date of Admission Before Ref Date W Date of Ref lt 30 days VW My filter The above filter was created using the following sequence e Right click and select Add Operator and then select Open Bracket e Right click and select Add Operator and then select Now e Right click and select Add Operator and then select Minus e Double click Date of Referral item and remove the operator e Right click and select Add Operator and then select Close Bracket e Right click and select Add Operator and then select Expr e Inthe blank row select gt in the Op
82. or MHS Overseas visitor HHS D M Co ms Co ha ct 0 2 2 3 3 4 July 2015 Page 61 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 MANIPULATING DATA IN VIEWS 9 1 Functions The functions described in this section perform aggregate or manipulative tasks on the data items in the View In all cases to add these functions to the view first add the item to the view then select the function from the dropdown list in the Function column Query Yiew Properties Referral view Function Data Items Hide Alias E N Date of Referral E E Count 8 Count Distinct Mini M anl 9 1 1 Count The Count function counts the number of records which have a value recorded in the field chosen This example produces a count of how many patients matching the filter criteria have something recorded in the field Days from Referral to Appointment Query Yiew Properties My view Count Days from referral to appointrn One row is returned SQL Results 1 rowls Count D ays from referral to appointment 012 July 2015 Page 62 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 1 2 Count Distinct The Count Distinct function counts the number of distinct unique values recorded in the field chosen It behaves like Count but eliminates duplicate values before the count is calculated This example produces a count o
83. ord does need to be included in the query results then the null can be substituted with a default value such as a zero Access and SQL Server databases use different syntax for this SOL Server databases The syntax to use 0 instead of Null for a data item called Valueltem is as follows IsNULL Valueltem 0 To use this syntax in a filter insert a Value row before and after the data item in the filter and type the relevant syntax in the Value column of the blank row Query Filter Properties my filter ISNULLI E a Valueltern E O 0 The above filter was created using the following sequence e Right click and select Add Operator and then select Value e Add ISNULL to the Value column of the blank row e Double click the ValueItem item and remove the operator e Right click and choose Add Operator then Value e Enter 0 in the Value column of the blank row Here is an example of the syntax in use in a filter Queries Views Filters Query Filter Properties Adjusted Wait lt 14 days Clinical Data view per GLOBAL piv system W Adjusted Wait lt 14 days cow SP My filter IsNULL 0 July 2015 Page 88 Chameleon Information Management Services Ltd InfoFlex QDM User Guide Access databases The syntax to use 0 instead of Null for a data item called Valueltem is as follows IF Valueltem 0 To use this syntax in a filter insert a Value row before and after the data item in t
84. osis The Options column allows you to specify custom formats for Dictionary Lookup Items that are dates This is free text data entry but the date format must be a valid date format eg if you just want to display the month enter mmm 3 3 3 Sort The Sort option allows you to order the data by a particular column To use this option select either Asc or Desc in the Sort column for the item which you wish the data to be sorted by 3 3 4 Group by Group by is used in conjunction with the Count operator to calculate occurrences of a particular value in an item see section 8 1 Occurrence Counting and also with aggregated values see section 9 3 Aggregated values grouped by patient 3 3 5 Hide This is a simple tick option and allows you to hide the results from this column When the query is run that column of data is simply omitted This option is particularly useful if you wish to temporarily anonymise data 3 3 6 Alias Alias If you wish to specify your own column heading for a particular item enter it in the Alias column By default no alias is set and the item name is used as the column heading July 2015 Page 23 InfoFlex QDM User Guide Chameleon Information Management Services Ltd 3 4 Exercise In the Training group 1 group create a new view called Referral view Add the following items From the Patient Registration event Hospital number Surname Date of Birth From the Referral event Details panel
85. ox is displayed listing all the items that have been set as prompt items The Parameter column contains the help text that has been entered for the item Parameters Start date End date lt Start date End date T The results are returned in the Results grid as usual coL Results 4 rows Hospital Number Date of Referral 123456 2r 02000 O04 2170172003 345670 1070772000 004 456709 0170772000 001 do fb54 20r 07r 2000 O02 July 2015 Page 47 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 6 2 Exercise l Create a filter in Training group 1 called Prompt filter Set it to prompt for a date range for Date of Referral ie prompt for earliest and latest Date of Referral Set prompt text for each parameter Save the filter 2 Create a new query in the Training group 1 called Prompt query Select Referral View and Prompt filter Save and run the query Enter dates of 1 1 2000 and today Then rerun the query with dates of 1 7 2000 and 31 7 2000 July 2015 Page 48 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 7 QUERY PARAMETERS 7 1 Types of query Simple queries use data from one off events or data from the first level of a repeat event Complex Queries can use Complex Views and Complex Filters Complex Views can include data items from several events including repeat events at several levels Complex Filters can include criteri
86. query parameters which control how the view and filter are linked together In Query Design Manager you can view and edit existing views filters and queries and you can create new views filters and queries You can also run queries and export the resulting data Queries are used in many places in InfoFlex eg e Data Analysis e Scheduler e Reporting e Bed Manager e Work List e Add Ins eg the Extract Add In for the production of e Data Entry subject searches data extracts Within QDM the following symbols are used represents a query group represents a query represents a view YW represents a filter Query Design Manager will be referred to as QDM throughout this manual 1 2 Domain and data view queries Queries can be defined both within domains and within data views Queries cannot be moved or copied between a domain and a data view so it is important to define your queries in the correct location The location a query should be created in is governed by the purpose of the query For example queries for use in documents and reports are defined on the domain since documents and reports are defined at domain level However queries for use in Data Analysis are defined in the Data View since access to Data Analysis is granted by data view and the data items available need to be limited by user permission When defining a query for a particular purpose it 1s therefore important to know whether the query should be in a domain or
87. r is prompted for a value to include in the calculation Text The user is prompted for text to include in the calculation Date The user is prompted for a date to include to the calculation Date time The user 1s prompted for a date time to include to the calculation July 2015 Page 90 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 10 5 Using Subfilters in Filters There can be common sub elements in filters Rather than create the common elements within each filter a subfilter can be created that is then re used in all the relevant filters For example a time period using prompt parameters might be a common element of several filters This prompted time period can be defined as an individual filter and then re used in other filters as necessary Creating a subfilter reduces maintenance since when changes are needed they can be made once to the subfilter rather than to every filter which uses the common element However care must also be taken since changing a subfilter will globally affect all filters that use the subfilter We recommend using specific Query groups to identify subfilters In the example below the filter called Date prompt subfilter prompts the user for start and end dates Query Filter Properties Date prompt sublilter A Date of Referral Enter start date JAND E b Date of Referral f Enter end date To use this filter as a subfilter in another filter first create the second filter
88. re Clarke Ho 01701 1922 no July 2015 Page 77 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 6 1 Expressions without the formula builder In earlier versions of InfoFlex before the formula builder was available in view definition expressions could be added by using the Value operator to add the necessary syntax to the view This method of calculating expressions is still supported and existing expressions created in this way can be edited However wherever possible we recommend that expressions are created using the formula builder This example includes a calculation of the difference between the Appointment Date and Date of Referral using the DATEDIFF function Query Yiew Properties My view Forcon Desens Hospital Humber Sumame Date of Referal Appointment date DATEDIFF d Date of Referral Appointment date The view was created using the following sequence e Double click Hospital Number item e Double click Surname item e Double click Date of Referral item e Double click Appointment Date item e Right click and select Add Operator and then select Value e Inthe blank row enter DATEDIFF d in the Data Items column e Double click the Date of Referral item e Right click and select Add Operator and then select Value e Inthe blank row enter a comma in the Data Items column e Double click the Appointment date item e Right click and select Add Operator and then select Close Bracket
89. re that the syntax of the commas in functions etc is correct When a calculation is created in a view the Alias column will need to be used to specify the column heading To add any of the above operators to a view right click in the view and choose the operator you require Query View Properties My view Function Dataltems Format Options Sot Group By Hide Alias b Oo O0 Date of Referral Delete Add Operator P Flus Minus Multiply Divide Close Bracket N Value Expr Mow Count July 2015 Page 79 Chameleon Information Management Services Ltd Example 1 InfoFlex QDM User Guide This example calculates a patient s weight loss by subtracting one weight from another A column heading for the calculation has been entered in the Alias column These results are returned Example 2 lasimum weight Curent weight lasimum weight Curent weight SOL Results 4 rows Masinurn weight Current weight megn loss oag0uo no00 0 L L L L L L L Alias Weight loss This example returns two columns The first shows the Date of Referral and the second shows a calculation of the number of years between the Date of Referral and today s date A column heading for the calculation has been entered in the Alias column Query Yiew Properties Hy view a rr ee Date of Referral NOW These results are returned Date of Refe
90. result set Setting Distinct Rows to Yes prevents this happening By default Distinct Rows is set to No See section 7 3 Distinct Rows for further details 5 2 7 Context The Context parameter is relevant when queries are used in documents and reports The Context parameter sets a lowest common parent for the data that is returned in the document See the Report Definition and Document definition user guides for further information 5 2 8 User defined User Defined allows users to write their own SQL queries rather than defining views and filters See section 7 6 User defined for further details July 2015 Page 40 Chameleon Information Management Services Ltd 5 3 Running a query InfoFlex QDM User Guide After defining a query save it by pressing F5 or the Save button Query Design Manager Query Definition File Edit View Dovel Yiews Filters Queries E I Clinical Data view This query i for View View 1 Filter Filter 1 Link at Outer Join Mo i To view the query results press the Run button on the toolbar Note that if you make any changes to a query for instance selecting a different view or filter or changing the Link or Join and then run the query without saving there will be a prompt to save If you save the new saved query will be run and if you do not save the unsaved query will be run rather than the saved query The query results are displayed in the Result
91. rral r 365 coL Results 12 rows Date of Referral 0870172000 10 2565275431 255 05 03 2000 10 1307631 59563 02 04 2000 10 054050830 965 3070572000 3 6951 4672120751 0170672000 3 5696672691527 0170772000 9 5074754003306 1070772000 9 76261795400422 2rr0rr2000 9 73624261161847 d C00 C0OC0OCO O OOO 000 0O O No of pears Note that this example can also be carried out using formula builder Formula builder should be used wherever possible for calculations July 2015 Page 80 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 8 Summary of Operators that can be added to views Below is a summary of the operators that can be added to views Adds a row containing Adds a row containing Adds a row containing Adds a row containing Adds a row containing Adds a row containing Adds a blank row into which free text including numbers can be added Adds a row and automatically displays the InfoFlex formula builder For use with expressions that do not need InfoFlex items Adds the Now function Ow Adds the Count operator see section 8 July 2015 Page 81 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 9 Exercises 9 9 1 Functions and multiple functions The following exercises all use the Clinical Data view and items from the Referral event In the Clinical data view create a new query group in the Training query group called Functions exercis
92. s grid below the Queries tab Query Design Manager Query Definition Eile Edit view Ele elan Y Quenes Views Filters Queries Query Properties Referral query J Clinical Data view Description This query i for i Group 1 View View E E jsmith Filter Filter 1 Referal Queres Link at G Queries Outer Join No oo Referal query ae Distinct Rows Mo Context User Defined Mo Number of records rae ae Click a column heading to order returned is indicated here the results by that column SQL coL Results 14 row s Hospital Humber Date of Birth Date of Referral Consultant Source of Referral Initial diagnosis of bas Bartley 01701 1970 99999 99999 Missing 96654 Bates 0170171930 280r 2000 21230014 Clark 3 General Surgeon ABSD Localized enlarged lymph nodes EEE Browr 01 01 1945 99959 99999 Missing FB S90 Clarke 01071960 05 03 2000 21230016 Evans 4 General Physician F204 Post schizophrenic depression 456789 Gate 017 0171955 017 0772000 21230009 Hughes 3 General Surgeon J069 Acute upper respiratory infection 123456 Green 01071965 2rfOF 2000 41230004 Brown 9 4 General Physician C099 Malignant neoplasm of tonsil unspecified 890123 Johnston 01701 1935 O1O62000 21230016 Evang 1 A amp E C329 Malignant neoplasm of larri 234567 Jones 01071965 30 05 2000 1230002 Jones 1 A amp E DOSS Carcinoma
93. ston Johnston Trailing spaces have been removed Leading spaces have been removed 901 234 Miles Miles 98654 Bates Bates July 2015 Page 67 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 2 Multiple functions Several functions can be used in the same view This example finds the Min Max and Average values of the Days from Referral to Appointment in records that meet the filter criteria Query View Properties Range Avg Days Ref to Appt fem Otome Days from referral to appointment Days from referral to appointment Days from referral to appointment One row is returned SQL Results 1 rows Min D ays from referral to appointment Max Days from referral to appointment Avg Days trom referral to appointment agi 061 013 9 3 Aggregated values grouped by patient Functions can also be used to find the aggregated values by patient Any data items that are not being aggregated should have a tick in the Group By column This example returns the first and last date of radiotherapy for each patient meeting the filter criteria Quer Yiew Properties My view mooi i eee Alias E Hospital Number E Sumame E kanf Date of this radiotherapy A kiini Date of this radiotherapy O s O00 One row is returned for each patient that meets the filter criteria coL Results 4 rowfs Hospital Humber Min Date of this radiotherapy Max Date of this radiotherap
94. the sum of all the Doses in the records that meet the filter criteria Query Yiew Properties My view b Sumi Dose of other drug given One row is returned SQL Results 1 row s Sum Dose of other drug given 150 00 9 1 6 Avg The Avg function finds the average of the values that meet the filter criteria This example returns the average Age in records that meet the filter criteria Query Yiew Properties Referral view One record 1s returned soL Results 1 rows 9 1 7 StDevQ The StDev function finds the standard deviation of the values that meet the filter criteria This example returns the standard deviation of Age in records that meet the filter criteria along with the average Age Query Yiew Properties My view b Ava Days from referral to appointment LJ E StDev Days from referral to appointment LJ One row is returned SOL Results 1 row s Ayg Davs from referal to appointment StD ev D aps from referal to appointment O13 O14 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 1 8 Median The Median function should be used on numbers and returns the median value of the set of numbers For example Query View Properties Median Yat referral to appt Function Data Items Format Options sort 0 Meciant Vat from ret to 1st offered appt The median function does not include missing and unknown values in its calculation and ignores empt
95. thorax 123456 Green 01011965 317 0772000 p707 000 i depressior CO99 Malignant neoplasm of tonsil unspecified 93357654 Bates 01701719301 307 0772000 Pao 2000 3 nausea 5 weightloss R590 Localized enlarged lymph nodes 345675 Rose 01701719251 15 07 2000 NOVO 2000 7 bleeding pain C322 Malignant neoplasm of subglottis 456759 Gate 01701 19551 06 077 2000 1 07 2000 0 pain 3 nausea JO69 Acute upper respiratory infection 234567 Jones o701 1965 017 0772000 b005 2000 5 weightloss DOSS Carcinoma in situ of thyroid and other enda 890123 Johnston 01 01 1935 06 067 2000 1 06 2000 3 nausea 4 vomiting 99 Other C329 Malignant neoplasm of larns AB eS Clarke 01701 1 960 05 05 2000 95 03 2000 amp depression F204 Post schizophrenic depression regu Parisien 01 01 325 04 04 2000 hevn4 2000 99 Other J359 Chronic disease of tonsils and adenoids 301234 Miles 01701 1380 OT 000 08 01 2000 O pain 1 bleeding J039 Acute tonaillitis 111111 Smith 0170171950 l BABA EA Forbes 0170171975 l PITTI Griffiths 01 01 1920 i E ara poooog ee Hughes 0101 71990 ee es E l Referral event July 2015 Page 55 Chameleon Information Management Services Ltd Inner Join InfoFlex QDM User Guide However when the same view is run in the same query and no filter criteria but with an Inner Join only 14 records are returned The 5 records with no Referral event are omitted because Inner Join specifies that a record can only
96. ting a query Queries VIEW Filters EE i 7 Clinical Data view To define a query go to the Queries tab right click the query group you wish the query to belong to and choose New Expand Expand Branch Expand Tree New Query Group Pelete Query Group z A new query is displayed in the query group Auenes Views Filters It is represented by the symbol Fl F Clinical Data view Type a name for your Queries Views Filters query It is displayed l Query Properties Referral query next to the symbol and the name is also displayed in the titlebar of the main window You are now ready to define the properties of your query Filter Link at Outer Join Yes Distinct Rows Mo Context User Defined Mo July 2015 Page 38 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 5 2 Adding properties to a query The following properties can be set in a query 5 2 1 Description Query Properties Heferral query A free text description for reference Description This query ig for VIEW 5 2 2 View Query Properties Referral query To select a view use the button to display a list of the Description This query ts for available views The list displays UUA Event Counts the Global group and the user s La a Link at Be Analysis Group own named group as well as any G GLOBAL o t J i subgroups and the v
97. tment date bi E E E O Delete Add Operator P Add Frompt value Text Date Date Time July 2015 Page 86 Chameleon Information Management Services Ltd InfoFlex QDM User Guide The example in 10 2 labove can be adjusted to prompt for the number of days as follows Query Filter Properties My filter gt L DATEDIFF d Date of Referral LJ a O iz Appointment date O E m E gt o E Enter the number of days The above filter was created using the following sequence e Right click and select Add Operator and then select Value e Add DATEDIFF d to the Value column of the blank row e Double click the Date of Referral item and remove the operator e Right click and choose Add Operator then Value e Enter a comma in the Value column of the blank row e Double click the Appointment Date item and remove the operator e Right click and select Add Operator then Close Bracket e Right click and select Add Operator then Expr e Enter gt in the Operator column of the blank row e Right click and select Add Prompt then Value e Add the Help Text in the row with the Prompt column ticked July 2015 Page 87 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 10 3 Calculations in Filters using Fields containing Blanks If a record has a null value in any of the items used in a filter calculation that record will be not be returned in the query results If such a rec
98. ty Description 3 Clinical Data view View Filter Link at Outer Join No Distinct Rows No Context User Defined No July 2015 Page 12 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 15 Defining query groups To define a query group right click the parent group you wish to attach it to and choose New Query Group Queres WEWE Filters Queries pesi We Clinical Data view E Analysis Group come G LOBAL _ Expand Expand Branch Expand Tree New Query Group A new query group is displayed on the tree Queries VIEWS Filters Give the query group a name that describes the queries that will be defined within it and then press return Queres VIEWS Filters The query group is displayed on each of the Queries Views and Filters tabs Quenes Views Filters Queries Views Filters Filters I Clinical Data view EJ Analysis Group ae GLOBAL feral guernes July 2015 Page 13 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 1 6 Exercise Log into the InfoFlex CIMS General training database using the username training and the password training Go to Design Management and display the Clinical domain and the Clinical data view Ensure that both are unarchived Select the Clinical Domain and then go to the Actions menu and choose Query Design Manager Note that QDM displays the Clinical Domain
99. uery i PE Fs Quen DC PO H Quen DC 2 TPA Fs Quen OCS Page 9 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 1 4 2 Query groups in a data view Within each Data View a query group for Data Analysis queries is created by default Within this Analysis Group there are further default query groups a Global query group and a query group for each username Each user can see the Global group and the group corresponding to their own username This is true whether you are accessing data views in QDM from Design Management Data Analysis or any other function Administrators have the additional option to view all users named query groups Queries can be copied between groups within the Analysis group and the Administrator can copy queries between the named users groups but queries cannot be copied between data views Query subgroups can be created within each of these groups to enable management of the queries Queries created within the Analysis group are available whenever a function requires queries to be selected from a data view Fh Query G21 H FEW Query G41 i IB Query G22 oo EY Quen G42 by i gt A jsmith mi E jamith EI E My Group 1 Queries E Queries ew A Query OF WH Query 1 1 ee quer 08 oe FB query 1 2 hes EE Query 09 N E My Group 2 i E My Group 3 Queries E Queries E Query 2 1 Quen 3 1 E Query 2 2 n Guer 3 2 E My Group 4 Queres betes Fey
100. ults in a new unsaved worksheet If you wish to keep this file to use in the future you should save it in the format and location of your choice Paie Booki Microsoft Excel le C s cay Home Insert Page Layout Formulas Data Review View i NGHARAoOBeak A BASE SBE 8 7 9 C B c D E F 1 Hospital Number Surname Date of Birth Date of Referral Presenting Symptoms Initial diagnosis 2 660606 Smith 01 1940 20 12 02 3 678901 Mount 01 1945 02 08 00 headache 271 Traumatic haemothorax A 987654 Bates 01 1930 28 07 00 nausea weightloss R590 Localized enlarged lymph nodes 5 123456 Green 01 1965 27 07 00 bleeding vomiting de C099 Malignant neoplasm of tonsil unspecified 6 345678 Rose 01 1925 10 07 00 bleeding pain C322 Malignant neoplasm of subglottis 7 456789 Gate 01 1955 01 07 00 pain nausea J069 Acute upper respiratory infection 8 890123 Johnston 01 1935 01 06 00 nausea vomiting Othe C329 Malignant neoplasm of larynx 9 234567 Jones 01 1965 30 05 00 weightloss DO93 Carcinoma in situ of thyroid and other endocrine glands 10 789012 Parisien 01 1925 02 04 00 Other J359 Chronic disease of tonsils and adenoids 11 567890 Clarke 01 1960 05 03 00 depression F204 Post schizophrenic depression 12 901234 Miles 01 1980 08 01 00 pain bleeding J039 Acute tonsillitis 179 July 2015 Page 98 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 11 2 Export to File To export your query results to a file select yo
101. ur query on the queries tab in QDM If you wish run a preview of it Queries MEWE Filters CM Query Properties My Query Property el Clinical Data view Description We biy view Filter My filter Lirik at Outer Jor Mo Distinct Rows Yes Context User Defined Wo SQL Results 17 rowl s Consultant 111111 S mith 1 01 1950 19 04 2010 111111 Smith 01 01 1950 20 03 2010 20 04 2010 123456 Green 01 01 1965 27 07 2000 31 07 2000 21230004 B Go to the File menu and choose Export then File or press the Export to File button Query Design Manager Query Definition File Edit wiew Options Query Properties My Query Property Description WEIN bly view Filter Mly filter Link at Outer Jor Mo Distinct Rows Yes Context User Defined No SQL Results 17 row s Hospital Number Date of Birth Date of Referral Appointment date 111111 Smith 0170171950 1970472010 111111 Smith 0170171950 2070372010 2070472010 123456 Green 01701 1965 2f 07 2000 3170772000 412350 detida Brown 0170171945 2070172003 20 04 4003 Consul July 2015 Page 99 Chameleon Information Management Services Ltd InfoFlex QDM User Guide InfoFlex displays the x Filename CADOCUME TWWATTIM CIMALOC Save Results window a Cancel Main Column Names Details Headers Footers Column Deliriter f Comma C Tab Use quote marks Replace Carriage Return with Space
102. utton eee Hospital Number Sumame Date of Referral Appointment date Date of Referral Create the formula as shown below Edit Formula EE m x DATEDIFF d These results are returned SQL Results 20 rowfs Referral Date of Referral Referral Appointment date LI SUM ame Date of Referral Appointment date DATEDIFF d Date of Referral Appointment date Ed Hospital Number Referral Date of Referral Referral Appointment datej i Hospital Number Date of Referral Appointment date DATEDIFF d Date fReteralAppointmentD ate 111111 BEBEEE 3456 5 rog0l 2 bos 901234 901234 Smith Smith Rose Parigiert Mount Miles Miles 20 12 2002 107 0772000 02 04 2000 02 08 2000 0170172001 0870172000 20 01 2003 31 157 0772000 5 O4 04 2000 2 02 08 2000 0 0170272001 31 1270172000 4 July 2015 Page 72 Chameleon Information Management Services Ltd InfoFlex QDM User Guide Example 2 This example uses the IFMISSINGORUNKNOWN function to display free text if the Date of Referral is marked as missing F11 or as unknown F12 Edit Formula IFHISSINGORUNENOWN Referral Date of Referral Value not available O x O Referral Date of Referral E i Value not available o O O The function is displayed in the view Hospital Number Sumame IFMISSINGORUN EAN OWN Date of Referal Yalue not available F
103. vent this means that those records have no referral event Set Outer Join to No Save then re run the query This time 15 records are returned This is because records are only returned if all the events that are represented in the view exist July 2015 Page 57 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 7 5 Context The Context parameter is relevant when queries are used in documents and reports The Context parameter sets a lowest common parent for the data that is returned in the document See the Report Definition and Document definition user guides for further information 7 6 User defined Querying functions that are not currently available in QDM can be requested through the CIMS helpdesk Agreed functions that are developed are released to all customers However in the event of an urgent requirement users may write their own SQL instead of using InfoFlex views and filters Please contact CIMS helpdesk if you think you need to use this function If it is agreed that user defined SQL is required set User Defined to Yes and press the l button to display A User defined SQL box is displayed in which to enter your own SQL Note that on setting User Defined to Yes any selected view and filter are removed Quer Properties Referal query Description This query is for View Filter Link at Outer Join Ho Distinct Rows Mo Context ee User Defined les lt se eevee mer
104. xed value into a view right click the row above the fixed value and choose Add Operator then Value Quer View Properties My view Hospital Number Surname Date of Birth Delete Date of Referral Source of Refera Odd Operator Plus Hospital Hame Code Minus Multiply Divide 0000000 0000000 Consultant Open Bracket Close Bracket Expr A row is inserted in the view Type the fixed value directly into the Data Items column in the row Note that you must put double quotes around the value To create a blank column in the view type two double quotes in the Data Items column This example has two fixed values in it one a blank column the other with text A column heading can be entered in the Alias column Query View Properties My view Funetion Dataltems Format Options Sort Group By Hide Aias L L Hospital Humber Surname Date of Birth pma This produces a blank column Date of Referral My blank column OOOO Ooo OOOO Ooo Source of Referral Code and Meaning hello My fined value Consultant Code and Meaning Hospital Hame Code and Meaning These results are returned Column headings SQL Results 16 rows Hospital Number Date of Birth My blank column Date of Referral Source of Referal My fixed value 111111 Smith 01 01 1950 39999 0 GP hella AB1O0 St Matthew s Hospital 123456 Green 01 01 1965 27 07 2000 0 GP hella 71230004 Brown
105. y Green 3170772000 09709 2000 Jones 1171072000 157 0172001 Rose 2w 072000 3070972000 Johnston 0170772000 31708 2000 July 2015 Page 68 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 4 Concatenation It is possible to concatenate two strings in a view The symbol should be used in the syntax for SQLServer databases and the amp symbol for Access databases You can concatenate in the grid by adding the concatenation symbol as a value or an expression between the two items to be concatenated When using a value the concatenation symbol and any additional string should be entered in the Data Items column When using an expression the concatenation symbol and any additional string should be entered in the Function column In this example a value field has been added between the two items Forename Surname Forename Surname You can also concatenate a constant with a data item Strings to be concatenated must be contained within quotes Format Patient Forename SUM ane July 2015 Page 69 Chameleon Information Management Services Ltd InfoFlex QDM User Guide 9 5 Fixed Values in Views The Value operator can be used to insert a fixed value into a specific column or to insert a blank column For example there might be a fixed record format for a data extract where a column should always have the same value or should always be blank To insert a fi
106. y Details B Items tree in the data view niais Other management details B ai Return to theatre details E pe Treatment Details B Medication Details B ie Treatment complications B ae Radiotherapy details B Quenes Wiews Filters Query Yiew Properties Referral view Funeton Date tene Fema Hospital Murniber 2 sane ii seel Pass m Date of Birth Page 21 Chameleon Information Management Services Ltd InfoFlex QDM User Guide You can select as many items from as many different events as required You can also add the same item twice if necessary When you have finished save the view by pressing F5 or press the Save button Notice that as you add items to the view they turn red in the tree and the events and panels from which items have been selected are selected are also displayed in red F Query Design Manager iew Definition File Edit View Options mejam Y pl De Views Filters A Query View Properties Referral view Function Dataltems Format Options Sort Group By Hide Aias Hospital Humber 2 SR GLO BAL E E jsmith OO Sumame Forename Date of Birth Appointment date Date of Referral Consultant OOO OO O 0O 0 OoOjO OOo Source of Referral ony Appointment date daa Date of Referral l 1 Age al Hospital Name free at Consultant Sfc ource of Referral ies ra Category p
107. y data Important Note The median function requires the database to be on Sql Server version 2005 or greater The server on which the database resides will require Microsoft Net 2 0 Framework to be installed The median function requires a sql assembly and function to be registered and this is done in a database update The Median function is not available on Access databases or on SQL Server version 2000 databases 9 1 9 Abs The Abs function returns the absolute positive value Negative numbers are returned as positive numbers This example returns all the values in the Days from Referral to Appointment as positive numbers Quer Yiew Properties My view b Abaf Daye from referral to appointment Note that even missing 88888 and unknown 99999 values are returned as positive SQL Results 10 rows Days from referral to appointment July 2015 Page 65 Chameleon Information Management Services Ltd 9 1 10 UpperQ Lower InfoFlex QDM User Guide The Upper and Lower functions set the case of the values to Upper or Lower This example Query View Properties My view E Forename Upper Sumame sets the Forename to lower case and the Surname to upper case coL Results 19 row s LowerlForename Upper urmame barry bill bill chris diane francis gardenia george greta helen jessica john johr 9 1 11 Ltrim Rtrim BARTLEY BATES BROWN CLARKE DAVIES FO
108. y of the items in the view a blank row is returned For example In this design a view is created which contains items from both the Patient Registration event and the Referral event In this example no filter criteria are defined so all patients are available Outer Join Function Data lterns Format Funetion Dataltems Ll Hospital Humber Sumame Date of Birth Appointment date dd fron Date of Referral ddran yy Presenting Symptoms Code and Meaning Initial diagnosis Code and Meaning When this view is run in a query with an Outer Join and no filter criteria 19 records are returned however the last 5 records do not show any data against the items from the Referral event The Appointment Date item is the identifier of the event and it is included in the view so we can be sure that the Referral event does not exist for these 5 records The 5 records are included because the Patient Registration event which is represented in the view does exist for them and an Outer Join will return a record as long as at least one event represented in the view exists SQL Resulte 19 row s Initial diagnosis 076543 Bartley 01701 1 370 04 08 2009 99999 BEBE Smith 0101 1 340 20 01 2003 O11 2 2002 The aveat Jadid Brown 0170171945 20 01 2003 99999 at 333333 Joseph 01 01 1985 20 01 2003 39999 Identifier of the Eragi Mout 01 01 41945 02 08 2000 2 06 2000 Referral event S2f1 Traumatic haemo

InfoFlex Query Design Manager User Guide

Contents

Download Pdf Manuals

Related Search

Related Contents