5 Replies Latest reply: Apr 7, 2016 8:26 AM by Karen Wallace RSS

    Interval match possible when a score:rank possibility is dynamic?

    Karen Wallace

      Dear users,

       

      I'm facing a challenge - with the Interval Match function is it possible to dynamically handle a benchmark file where each individual variable (question) and domain (summary section of variables) score to a ranking which is different for each instance?

       

      Using the below coding each score correctly fell into a "bucket" of ranges for percentile, e.g. score 70.4 fell within "Between 80th Percentile and 90th Percentile", but this isn't as precise as is required.

       

      We need to know specifically when a score falls within each percentile, for example, a score of a 70.4 in one domain is in the 85th percentile, whereas in another domain, the score of 70.4 is in the 70th percentile.

       

      Another issue is whether to embed this coding within the edit script or to have it as an object; the latter seems to allow more flexibility for the end user on the front end.


      Much appreciation for your help!

       

       

      Benchmark:
      LOAD PERIOD,
      SCORE_TYPE,
      SERV_ABBR,
      PG_ABBR,
      VAR_NAME,
      ANALYSIS_ID,
      LABEL as Domain,
      RANK,
      num(SCORE, '##.##%') as SCORE
      FROM
      [S:\DSA\Projects\Active\Patient Satisfaction\Mapping Tables\Benchmark Mapping 00.csv]
      (
      txt, codepage is 1252, embedded labels, delimiter is ',', msq)
      WHERE(SCORE_TYPE = 'TOP_BOX' And SERV_ABBR = 'IN CAHPS');

      LOAD HCAHPS_VARIABLE,
      HCAHPS_VALUE,
      PTKEY,
      Eff_Response,
      Top_Box,
      All_Responses,
      CMS_Distinction,
      Domain,
      INSVCCHECK,
      QUESTION_TEXT
      FROM
      [....qvd]
      (
      qvd);

      IntervalTest:
      LOAD * INLINE [
      SCORE_Start, SCORE_End, SCORE_RANKING
      90.00, 99.99, '>= 90th Percentile'
      80.00, 89.99, 'Between 80th Percentile and 90th Percentile'
      70.00, 79.99, 'Between 70th Percentile and 80th Percentile'
      60.00, 69.99, 'Between 60th Percentile and 70th Percentile'
      0.00, 59.99, '< 60th Percentile'
      ]
      ;

      //Link the field RANK to the time intervals defined by the fields Start and End.

      Inner Join IntervalMatch (RANK)

      LOAD SCORE_Start, SCORE_End
      Resident IntervalTest;

        • Re: Interval match possible when a score:rank possibility is dynamic?
          Stefan Wühl

          Look into the extended syntax version of Intervalmatch() where you can define keys for the matching, like your domain field.

           

          edit:

          IntervalMatch (Extended Syntax) ‒ QlikView

          IntervalMatch ‒ QlikView  (see example 2)

            • Re: Interval match possible when a score:rank possibility is dynamic?
              Karen Wallace

              Thank you everyone! Trying out these solutions this morning and will update later!

               

              >>> swuehl <qcwebmaster@qlikview.com> 4/6/2016 4:32 PM >>>

               

               

               

               

               

               

               

              Interval match possible when a score:rank possibility is dynamic?

              reply from swuehl in Scripting - View the full discussion

               

               

               

              Look into the extended syntax version of Intervalmatch() where you can define keys for the matching, like your domain field.

              Reply to this message by replying to this email, or go to the message on Qlik Community

              Start a new discussion in Scripting by email or at Qlik Community

              Following Interval match possible when a score:rank possibility is dynamic? in these streams: Inbox

               

               

               

               

              © 1993-2016 QlikTech International AB | Copyright & Trademarks | Privacy | Terms of Use | Software EULA

               

               

              #####################################

              This message is for the named person's use only.  It may

              contain private, proprietary, or legally privileged information. 

              No privilege is waived or lost by any mistransmission.  If you

              receive this message in error, please immediately delete it and

              all copies of it from your system, destroy any hard copies of it,

              and notify the sender.  You must not, directly or indirectly, use,

              disclose, distribute, print, or copy any part of this message if you

              are not the intended recipient.  Health First reserves the right to

              monitor all e-mail communications through its networks.  Any views

              or opinions expressed in this message are solely those of the

              individual sender, except (1) where the message states such views

              or opinions are on behalf of a particular entity;  and (2) the sender

              is authorized by the entity to give such views or opinions.

               

            • Re: Interval match possible when a score:rank possibility is dynamic?
              Sunny Talwar

              May be use Extended Interval Match for your scenario. But you would need to specify in your inline table which domain has which value falling under what percentile.

               

              IntervalMatch (Extended Syntax) ‒ QlikView

              Qlikview Tutorial: Extended IntervalMatch

                • Re: Interval match possible when a score:rank possibility is dynamic?
                  Sunny Talwar

                  Basically this is how the script would look (loosely)

                   

                  Benchmark:
                  LOAD PERIOD,
                  SCORE_TYPE,
                  SERV_ABBR,
                  PG_ABBR,
                  VAR_NAME,
                  ANALYSIS_ID,
                  LABEL as Domain,
                  RANK,
                  num(SCORE, '##.##%') as SCORE
                  FROM
                  [S:\DSA\Projects\Active\Patient Satisfaction\Mapping Tables\Benchmark Mapping 00.csv]
                  (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
                  WHERE(SCORE_TYPE = 'TOP_BOX' And SERV_ABBR = 'IN CAHPS');

                  LOAD HCAHPS_VARIABLE,
                  HCAHPS_VALUE,
                  PTKEY,
                  Eff_Response,
                  Top_Box,
                  All_Responses,
                  CMS_Distinction,
                  Domain,
                  INSVCCHECK,
                  QUESTION_TEXT
                  FROM
                  [....qvd]
                  (qvd);

                  IntervalTest:
                  LOAD * INLINE [
                  SCORE_Start, SCORE_End, SCORE_RANKING, Domain
                  90.00, 99.99, '>= 90th Percentile', blah-blah
                  80.00, 89.99, 'Between 80th Percentile and 90th Percentile', blah-blah
                  70.00, 79.99, 'Between 70th Percentile and 80th Percentile', blah-blah
                  60.00, 69.99, 'Between 60th Percentile and 70th Percentile', blah-blah
                  0.00, 59.99, '< 60th Percentile', blah-blah
                  ];

                  //Link the field RANK to the time intervals defined by the fields Start and End.

                  Inner Join IntervalMatch (RANK, Domain)
                  LOAD SCORE_Start,

                            SCORE_End,

                            Domain
                  Resident
                  IntervalTest;

                    • Re: Interval match possible when a score:rank possibility is dynamic?
                      Stefan Wühl

                      Re-reading your post, it might be better to calculate the decile the score falls into in the frontend, using fractile() function.

                       

                      Creating some sample data

                       

                      
                      LOAD *, 
                        Round(NORMINV(RAND(), 100*Sqrt(Domain), 10*Domain)) as Score,
                        iterno() as SocreID
                      WHILE iterno() <=100;  
                      LOAD dual('Domain '&recno(),recno()) as Domain
                      AutoGenerate 3;
                      

                       

                      Then e.g. create a chart with dimensions ScoreID and Domain, and as expression:

                       

                      =Score &

                      '  ('&

                      if(Score <= fractile(TOTAL<Domain> Score, .1), ' 0% to  10%',

                      if(Score <= fractile(TOTAL<Domain> Score, .2), '10% to  20%',

                      if(Score <= fractile(TOTAL<Domain> Score, .3), '20% to  30%',

                      if(Score <= fractile(TOTAL<Domain> Score, .4), '30% to  40%',

                      if(Score <= fractile(TOTAL<Domain> Score, .5), '40% to  50%',

                      if(Score <= fractile(TOTAL<Domain> Score, .6), '50% to  60%',

                      if(Score <= fractile(TOTAL<Domain> Score, .7), '60% to  70%',

                      if(Score <= fractile(TOTAL<Domain> Score, .8), '70% to  80%',

                      if(Score <= fractile(TOTAL<Domain> Score, .9), '80% to  90%','90% to 100%')))))))))

                      &')'

                       

                       

                      2016-04-07 00_23_28-QlikView x64 - [C__Users_Stefan_Downloads_comm212370.qvw_].png