5 Replies Latest reply: Nov 4, 2016 12:25 PM by Brit Nicholson RSS

    Help needed in String Function

    Mohammed Mukram

      Dears,

       

       

      I have an exam system where 50 Questions are available.

      the questions are multiple choose answers where student can select more than 1 answer.

       

      suppose 1 student selected for QuestionA 1st answer

      and 1 more student selected for QuestionA 1st&2nd Answer.

       

      so i want to see how many students for QuestionA selected 1st Answer and how many selected 2nd Answer.

      from the above example we can say 1st answer two students and 2nd answer one student.

       

      kindly find the attached sample data for the same.,

       

       

      Thanks,

      Mukram.

        • Re: Help needed in String Function
          m w

          See the attached qvw

            • Re: Help needed in String Function
              Piet Hein van der Stigchel

              I would organize your data differently, store the answers seperately using the subfield function and the questions in a question field:

               

               

               

              Tmp:

              LOAD * Inline

              [

              ID , Question1, Question2, RESULT

              101 , 1 , 2 , pass

              102 , 1&3 , 3&2 , pass

              103 , 1&2&3&4 , 5 , pass

              104 , 6 , 11 , Fail

              105 , 8 , 5 , Pass

              106 , 9 , 3 , Fail

              107 , 11 , 2 , Pass

              108 , 5 , 1&4&9 , pass

              109 , 3 , 1 , pass

              110 , 2 , 1 & 3 , pass

              111 , 1&4&9 , 1&2&3&4 , Fail

              112 , 2 , 6 , Pass

              113 , 3 & 2 , 8 , Fail

              114 , 5 , 9 , Pass

              115 , 11 , 8 , pass

              116 , 5 , 9 , pass

              117 , 3 , 11 , pass

              118 , 2 , 5 , Fail

              119 , 1&4&9 , 3 , Pass

              120 , 1 , 2 , Fail

              121 , 1 & 3 , 1&4&9 , Pass

              122 , 1&2&3&4 , 1&4&9 , pass

              123 , 6 , 11 , pass

              124 , 8 , 5 , pass

              ]

              ;

               

               

              Data:

              CrossTable(Question,Answer,2) LOAD

              ID,

              RESULT,

              SubField(Question1,'&') as Question1

              Resident Tmp;

               

               

              CrossTable(Question,Answer,2) LOAD

              ID,

              RESULT,

              SubField(Question2,'&') as Question2

              Resident Tmp;

               

              See attached

              • Re: Help needed in String Function
                Mohammed Mukram

                Dear m w,

                 

                thanks for your reply and solution.

                 

                I have 500000 Records in my table. when i apply your solution on that data the new table exploding the data.

                 

                is there anyway without duplicating the records?

                 

                Thanks,

                Mukram.

              • Re: Help needed in String Function
                Kamiel Rajaram

                Hi,

                Hope this helps

                 

                Regards

                Kamiel

                • Re: Help needed in String Function
                  Brit Nicholson

                  This works for your test data, but it's very narrowly tailored for your test data:

                   

                  For your expression, enter this: Count(DISTINCT ID)

                   

                  For your dimension: =Pick(Wildmatch(Q1,1,'1 &*','1&*',2,'*&2*','* 2*',3,'* 3','*&3*','*3 *',4,'*&4*',5,6,7,8,9,'*&9',10,11),
                  1,1,1,2,2,2,3,3,3,3,4,4,5,6,7,8,9,9,10,11)

                   

                  If your real data have different types of formats, you're going to have to apply some rules to your data, i.e. always use a space between ampersands and numbers or never include a space. Then you can probably develop a pretty simple "Pick(Wildmatch())" formula.