for each loop in for each + select statement

[#1]

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 ?

[alextimofeyev]

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?

[#3]

yes, that's mistake,

this is sample data

[alextimofeyev]

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;

[alextimofeyev]

Also, you could avoid using the loop altogether by expanding the condition in if():

if(locid=Previous(locid) and variable=Previous(variable),...)

[maxgro]

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;