5 Replies Latest reply on Sep 12, 2012 4:51 PM by RicardoVin

    Crosstab using ExecuteSQL

    RicardoVin

      Title

      Crosstab using ExecuteSQL

      Post

      Hello,

      I have a table like this:

      Exam|Date |Result

      ---------------------------------------

      Exam101-01-2012 10

      Exam201-01-201212

      Exam101-02-201211

      Exam201-03-201214

      XYZ

       

      I can have many kinds of exams performed during a long period.

      I need to show this data following the layout below

       

      Exam|Jan-01-2012   |Jan-02-2012   |Y. . . (as many as in the table)

      -----|-------------------------------------------------

      Exam1|1011-

      Exam2|1214-

      X|--Z

      .

      .

      .

      (as many as in the table)

       

      In MS Access it is very easy to do that using the SQL statement

      -----------------------------------------

      TRANSFORM First(Table_Exam.Result) AS FirstOfResult

      SELECT Table_Exam.Exam

      FROM Table_Exam

      GROUP BY Table_Exam.Exam

      PIVOT Format(Date,"mmm/dd/yyyy")

      -----------------------------------------

      However, this SQL statement doe not works in Filemaker using ExecuteSQL.

      All solutions I could find works only for a defined number of columns, but in my case the columns change every day as well as the kind of exam .

      Does anybody knows how to get it working

       

      Thank you.

        • 1. Re: Crosstab using ExecuteSQL
          RicardoVin

          The table format was lost after the post submition,

          Here you are the correct format

          Exam  |   Date           |  Result

          ---------------------------------------

          Exam1     01-01-2012     10

          Exam2     01-01-2012     12

          Exam1     01-02-2012     11

          Exam2     01-03-2012     14

          X            Y                  Z

           

          and

           

          Exam  |  Jan-01-2012  |  Jan-02-2012  |  Y. . . (as many as in the table)

          -----------------------------------------------------

          Exam1    10                 11                   -

          Exam2    12                 14                   -

          X           -                   -                    Z

          .

          .

          .

          (as many as in the table)

          • 2. Re: Crosstab using ExecuteSQL
            philmodjunk

            but in my case the columns change every day

            Please explain how they change. Any method you choose will tend to encounter limitations in the number of columns on your screen at one time, but there are ways to flexibly control which data appears in each of those columns.

            Are the result values shown data for a single exam or an aggregate value such as a total or average?

            • 3. Re: Crosstab using ExecuteSQL
              RicardoVin

              Hi PhilModjunk,

              Thank you for your reply.

              The source of the information is the table:

               

              Patient Exam Date Result
              1 Exam_Name1 01-Jan-2012 10
              1 Exam_Name2 01-Jan-2012 4
              1 Exam_Name1 04-Jan-2012 12
              1 Exam_Name3 04-Jan-2012  123
               ... ...  ...  ... 

               

               

              It is possible to have more than 50 different exams that can be performed as long as the patient stays at the hospital. It can be 1 week, can be 30 days. It is possible to do 1, 2 .... 10 exams in the same day and those exams are not necessarily repeted in the next day (or next necessity).

              The information reported in the crosstable is just the value observed in the exam. There is no need to aggregate them.

              So, the table content will vary a lot depending on the patient needs.

              The report will be done based only in one patient at time. 

              Meanwhile, I have created a table where the columns are the most common exams and the lines are the dates and values, but this solution is limited since depends on layout modification in case we have more exams.

              The solution will work on desktop, Ipad and Iphone, but this output will be used in Desktop and Ipad.

              Currently I am showing the data thru a portal as large as the number of fields on the screen and the user need to move the screen with horizontal finger movement. Althoug it is working, I was asked to make it more flexible.

               

              Searching in the internet I found the MMQuery plug-in that seems that has commands to create a table and change its content (fields and data). So I could create a temporary table based on the patient information to display data and after that delete it.

              Any suggestion will be welcome.

              Thank you.

              • 4. Re: Crosstab using ExecuteSQL
                philmodjunk

                My point is that your screen (or printed page) is only so wide. There's a limit to the number of columns that will fit before the page or edge of your screen cuts off the column.

                The interesting question in this case is which, in general will be the larger set of values for a long term/large number of tests patient. In cross tabs on any application, the set expected to vary the least is usually displayed in columns with the larger/more variable set of values listed in rows.

                I'd be inclined to use sub summary layout parts (when sorted by Exam ID) to get the rows of data grouped by Exam and would use one row filtered portals to display columns of data by date. The filter expressions can work from a "relative date range" where column 1 is the earliest specified date in the date range and the far right column would be the last date in the range specified. A script can set this range from the existing data as a starting point and then if you need to "scroll" the data horizontally, a different script can modify the date range specified to move forward or back in time.

                The portals would be based on a self join relationship that links to these test results by patient and Exam ID fields. (2 pairs of fields in one relationship--also known as a multipredicate relationship.)

                Ps. I'm not a health care professional, but a family member is a "frequent flier" when it comes to ER's and hospital admittance--aren't there some tests performed more than once in a given day? How would you chart that data in this cross tab?

                • 5. Re: Crosstab using ExecuteSQL
                  RicardoVin

                       Your suggestion was one I was considering before trying to find a different way, But it seems to be the best one for this case.

                       I came up with a different proposal and the guys accepted it instead of the table. The new solution creates a graphic where Y axis is the Exam value and X axis is the date. Nothing new so far, but I included in the chart view, 5 combo boxes where the users can choose the exams they want to analyse at the same time. We could used different ways to select the exams, but they liked the combo box. Once the user change the exam, the graphic is refreshed automatically. 

                       Even though, I will implement the suggestion in an additional layout.

                       Answering your PS question, yes, depending on the patient situation, it is necessary to follow up some exam numbers to see if, for example, the treatment is working. It is just one example. There are other reasons to do that.    In our case, it was conventioned that we will store the last exam value when performed in the same day.

                       If you have more ideas or questions, please let me know.