5 Replies Latest reply: Apr 27, 2017 1:48 PM by Massimo Grossi RSS

    for each loop in for each + select statement

    Zbigniew ?elazek

      Dear All

       

      I need help with this case in qlik sense ent

       

      I have postgres with db

       

      data, variable, value, locid

       

      2017-04-27 11:00:00,     var1,     111,     1

      2017-04-27 11:00:00,     var2,     222,     1

      2017-04-27 11:00:00,     var3,     333,     1

      2017-04-27 11:00:00,     var1,     423,     2

      2017-04-27 11:00:00,     var2,     423,     2

      2017-04-27 11:00:00,     var3,     423,     2

      2017-04-27 12:00:00,     var1,     111,     1

      2017-04-27 12:00:00,     var2,     222,     1

      2017-04-27 12:00:00,     var3,     333,     1

      2017-04-27 12:00:00,     var1,     423,     2

      2017-04-27 12:00:00,     var2,     423,     2

      2017-04-27 12:00:00,     var3,     423,     2


      I would like to have for each variable and for each locid diff between each variable, like this


      data                              variable     value     locid     diff

      2017-04-27 11:00:00,     var1,          111,          1    

      2017-04-27 11:00:00,     var2,          222,          1

      2017-04-27 11:00:00,     var3,          333,          1

      2017-04-27 11:00:00,     var1,          423,          2

      2017-04-27 11:00:00,     var2,          423,          2

      2017-04-27 11:00:00,     var3,          423,          2

      2017-04-27 12:00:00,     var1,          121,          1     10

      2017-04-27 12:00:00,     var2,          242,          1     20

      2017-04-27 12:00:00,     var3,          353,          1     20

      2017-04-27 12:00:00,     var1,          449,          2     26

      2017-04-27 12:00:00,     var2,          445,          2     22

      2017-04-27 12:00:00,     var3,          446,          2     23




      T1:

      SELECT

         data,

        variable,

        value,

        locid

      FROM table1;

       

       

      For Each id in FieldValueList('locid')

      LET id_Add = Chr(39)&id&Chr(39);

       

      LOAD

           data,

           variable,

           value,

            value - Previous(value) AS Diff;

      WHERE locid = "$(id_ToAdd)";

       

      next id

       

       

      but in this case in need also to implement for each loop in for each loopto have diff value for each variable and locid

       

      is it possible in qlik ?

       

       


        • Re: for each loop in for each + select statement
          Alex Timofeyev

          Zbigniew,

           

          in your first table variable values are the same each hour. E.g. var1 at locid=1 is 111 both at 11:00 and at 12:00. Is this a mistake?

          • Re: for each loop in for each + select statement
            Alex Timofeyev

            You can do it like this:

             

            T1:

            LOAD *;

            SELECT

               data,

              variable,

              value,

              locid

            FROM table1

            order by locid, variable, data;

             

             

             

            For Each id in FieldValueList('locid')

             

            T2:

            Load

                 data,

                 variable,

                 value,

                  if(variable=Previous(variable),value - Previous(value)) AS Diff,

                  locid

            Resident T1

            where locid='$(id)';

             

            next id

             

            drop table T1;

            • Re: for each loop in for each + select statement
              Massimo Grossi

              try

               

              tmp:

              load * inline [

              data                      ,        variable ,    value ,    locid ,    diff

              2017-04-27 11:00:00,    var1,          111,          1   

              2017-04-27 11:00:00,    var2,          222,          1

              2017-04-27 11:00:00,    var3,          333,          1

              2017-04-27 11:00:00,    var1,          423,          2

              2017-04-27 11:00:00,    var2,          423,          2

              2017-04-27 11:00:00,    var3,          423,          2

              2017-04-27 12:00:00,    var1,          121,          1  ,  10

              2017-04-27 12:00:00,    var2,          242,          1  ,  20

              2017-04-27 12:00:00,    var3,          353,          1  ,  20

              2017-04-27 12:00:00,    var1,          449,          2  ,  26

              2017-04-27 12:00:00,    var2,          445,          2  ,  22

              2017-04-27 12:00:00,    var3,          446,          2  ,  23

              ];

               

              final:

              NoConcatenate load

                *,

                if(variable=peek('variable') and locid=peek('locid'), value - peek('value')) as newdiff

              Resident tmp

              order by variable, locid, data;

               

              DROP Table tmp;