5 Replies Latest reply: Nov 9, 2015 12:37 PM by Roger Grossi RSS

    Count the number of changes

    Roger Grossi

      I have these two tables:

       

      table 1:

      LOAD * INLINE [

      Data,State,Table 

      a, 1, table 1

      b, 1, table 1

      c, 2, table 1   

      ];

       

       

      table 2:

      LOAD * INLINE [

      Data,State,Table  

          a, 2, table 2

          b, 1, table 2

          c, 1, table 2

       

      ];

       

      And I want, after compare table 1 to table 2, that Result Table:

       

      Changes, Nº

      State1to2, 1

      State2to1,2

      State1to3,0

       

      How can I do that? Thank you!

        • Re: Count the number of changes
          Stefan Wühl

          I think you want to have 'table 2' as Table field value in your second load:

           

          table1:

          LOAD * INLINE [

          Data,State,Table

          a, 1, table 1

          b, 1, table 1

          c, 2, table 1

          ];

           

           

          table2:

          LOAD * INLINE [

          Data,State,Table

              a, 2, table 2

              b, 1, table 2

              c, 1, table 2

          ];

           

          Then you can create a straight table with no dimension and three expressions:

          1 --> 2:

          =Sum( Aggr(If(Only({<Table = {'table 1'}>} State) = 1 and Only({<Table = {'table 2'}>} State) = 2,1),  Data))

          2 --> 1:

          =Sum( Aggr(If(Only({<Table = {'table 1'}>} State) = 2 and Only({<Table = {'table 2'}>} State) = 1,1),  Data))

          1 --> 3:

          =Sum( Aggr(If(Only({<Table = {'table 1'}>} State) = 1 and Only({<Table = {'table 2'}>} State) = 3,1),  Data))

          • Re: Count the number of changes
            Massimo Grossi

            table1:

            LOAD * INLINE [

            Data,State,Table

            a, 1, table 1

            b, 1, table 1

            c, 2, table 1  

            ];

             

            join 

            LOAD Data,State as State1,Table as Table1   INLINE [

            Data,State,Table 

                a, 2, table 2

                b, 1, table 2

                c, 1, table 2

            ];

             

            left join (table1)

            load *, State & '-->' & State1 as Change

            Resident table1;