6 Replies Latest reply: Feb 29, 2012 4:29 AM by Maria Hourigan RSS

    Joining Fields for Set Analysis

      Table one holds information on Students, Program, School and College,

      Table two the target table holds target records for NewHome/EU, NewOverseas, AllHome/EU, AllOverseas, NewHome/EU and NewOveseas by College, School and Program.

       

      In the script they are joined by Program, however we need to be able to join both in a number of ways when doing set analysis

       

      For example

      When the user selects the Home/EU filter (a field coming from the main table) the target number should display the All_Home/EU figures (a field coming from the target table). 

      When the user selects the New filter (a field coming from the main table) the target number should display New_Home/EU + New_Overseas (Coming from the target table) figures. and so on……

      .

      I have in part got this to work, however when I look at the chart the points for the actual from the main table and targets do not match on the charts.  Any advice gratefully appreciated.

      Regards

      Maria

        • Joining Fields for Set Analysis
          Jason Michaelides

          Can you post what you have so far, and some example data?  Much easier to help then...

              • Re: Joining Fields for Set Analysis
                Jason Michaelides

                Hmmm, it's taken a while to unpick your script a bit and I think you need to look carefully at your data model. At this stage I'll try some generic advice as it's difficult to do more without an example of the source data...

                 

                1. (not really related to the question) Consider using ApplyMap() with source or inline mapping tables instead of your IF() statements. Much more expandable.

                 

                2. Look at using a composite key to join the tables, something like PROGRAM_CODE & Residency & 'New/Continuing' AS %KEY.  Obviously you will need to substitute Residency and 'New/Continuing' for the logic that defines these fields. Essentially, you need to build a key that joins all the bits of data you want to compare between the two tables. You will then need to remove PROGRAM_CODE from one of the tables - ensure you leave it in the one that is guaranteed to have all possible values.

                 

                Hope this helps a bit - if you post some source data we can help further.

                 

                Jason

                  • Re: Joining Fields for Set Analysis

                    Hello Jason,

                    Thanks for getting back to me and thanks for the advice on ApplyMap I will have a bash at that this afternoon.

                     

                    I have attached the source data and the latest version of the QVW file I have been working on. This is not the actual data as we will use as we are still building the data model and waiting for the server to be installed. So I put together this test data to enable me to start practicing before we get started. However these are some of the issues we will encounter.

                     

                                     Table one is the Conversion_Reg_Data file

                                   Table 2 is the 20120221PlannedNumbersTemplateSIDV1 file   this table holds the target numbers.

                     

                    The biggest problem for me is that I have no control on the format of the the target numbers in the planning template. I had thought about using a composite key, but I can’t work out how I will join these fields as the key is dependent upon the selection of conditions - Region Home/EU or Overseas and New or ALL with the main data!

                    Regards

                    Maria