3 Replies Latest reply: Feb 1, 2017 4:09 AM by Max Goldshtein RSS

    Union 2 columns (Not in script)

    Max Goldshtein

      Hello

       

      Attached excel. I am trying to union 'Dr1', 'Dr2' and 'Dr3' to one column (not distinct). The Column B/N1, B/N2, B/N3 is the Dr answers.

      The last column B/N is the correct answer.

       

      I am comparing the Dr answer and the correct answer,

      So Union table should look like this (2 columns):

       

      rob  Yes

      joe  Yes

      max  Yes

      rina  No

      joe  Yes

      rob  No

      Nuna  No

      mike  Yes

      joe  Yes

      rina  Yes

      joe  Yes

      mike  Yes

       

      After the union i am going to count each name and the number of the correct answers, its gonna look like this (2 columns):

      rob  1

      joe  4

      max  1

      rina  1

      mike  2

       

      I don't want to do it in the script because I am going to have problems in filtering after. am i right?

       

      Thank you for helping!

        • Re: Union 2 columns (Not in script)
          Vineeth Pujari

          You will need to do it in script

           

           

          LOAD [case num],

               [Dr 1] as DR,

               [B/N 1] as Answered,

               [B/N] as CorrectAnswer,

               'DR1' as DR_Instance,

               if([B/N 1] = [B/N],1,0) as ResponseFLAG

          FROM

          [C:\Users\vp51284\Downloads\Test (1).xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          concatenate

          LOAD [case num],

           

           

               [Dr 2],

               [B/N 2],

               [B/N] as CorrectAnswer,

            'DR1' as DR_Instance,

                if([B/N 1] = [B/N],1,0) as ResponseFLAG

          FROM

          [C:\Users\vp51284\Downloads\Test (1).xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

          concatenate

           

          LOAD [case num],

               [Dr 3] as DR,

               [B/N 3] as Answered,

               [B/N] as CorrectAnswer,

               'DR1' as DR_Instance,

                    if([B/N 1] = [B/N],1,0) as ResponseFLAG

          FROM

          [C:\Users\vp51284\Downloads\Test (1).xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

          For Expression use Sum (ResponseFLAG)

          • Re: Union 2 columns (Not in script)
            Petter Skjolden

            Doing the correct transformations in your load script makes it much easier to get exactly what you want in your dashboard:

             

            2017-01-31 18_31_34-Qlik Sense Desktop.png

            2017-01-31 18_32_40-Qlik Sense Desktop.png