2 Replies Latest reply: Oct 14, 2016 3:40 PM by Massimo Grossi RSS

    How to diff the two columns

    kapil rathore

      I have two tables (xlsx) -

      Table -1

      ITEMVALUE
      ITEM-11,2,4,10,78
      ITEM-22,5,6,8
      ITEM-37,9,89,789

       

      Table 2-PROJECT

      PROJECTVALUE
      prj11
      prj12
      prj13
      prj155
      prj17
      prj19
      prj178

       

       

      i would like to generate a straight table which gives the matching and non matching VALUE with table2 like -

       

      ITEMMATCHINGNON MATCHING
      ITEM-11,2,784,10
      ITEM-225,6,8
      ITEM-37,989,789
        • Re: How to diff the two columns
          Sunny Talwar

          May be like this:

           

          PROJECT:

          Mapping

          LOAD VALUE,

            1;

          LOAD * INLINE [

              PROJECT, VALUE

              prj1, 1

              prj1, 2

              prj1, 3

              prj1, 55

              prj1, 7

              prj1, 9

              prj1, 78

          ];

           

          Table:

          LOAD ITEM,

            Concat(DISTINCT If(Flag = 1, LinkedValue), ',') as MATCHING,

            Concat(DISTINCT If(Flag = 0, LinkedValue), ',') as [NON-MATCHING]

          Group By ITEM;

          LOAD *,

            ApplyMap('PROJECT', LinkedValue, 0) as Flag;

          LOAD ITEM,

            VALUE,

            SubField(VALUE, ',') as LinkedValue;

          LOAD * INLINE [

              ITEM, VALUE

              ITEM-1, "1,2,4,10,78"

              ITEM-2, "2,5,6,8"

              ITEM-3, "7,9,89,789"

          ];


          Capture.PNG

          • Re: How to diff the two columns
            Massimo Grossi

            1.png

             

            MAP:

            Mapping LOAD      VALUE,     1

            FROM

            [https://community.qlik.com/thread/236229]

            (html, codepage is 1252, embedded labels, table is @2);

             

            S1:

            LOAD ITEM,

              ApplyMap('MAP', SVAL, 0) as FLAG,

              SVAL

            WHERE Len(Trim(ITEM)) > 0;

            LOAD ITEM,

                 subfield(VALUE, ',') as SVAL

            FROM

            [https://community.qlik.com/thread/236229]

            (html, codepage is 1252, embedded labels, table is @1);

             

            S2:

            LOAD ITEM,

              Concat(SVAL, ',', SVAL) as MATCH

            Resident S1

            WHERE FLAG=1

            GROUP BY ITEM;

             

            join (S2)

            LOAD ITEM,

              Concat(SVAL, ',', SVAL) as NOMATCH

            Resident S1

            WHERE FLAG=0

            GROUP BY ITEM;

             

            DROP Table S1;