Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

for each loop in for each + select statement

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 ?


5 Replies
alextimofeyev
Contributor II

Re: for each loop in for each + select statement

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?

Not applicable

Re: for each loop in for each + select statement

yes, that's mistake,

this is sample data

alextimofeyev
Contributor II

Re: for each loop in for each + select statement

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
Contributor II

Re: for each loop in for each + select statement

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

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

MVP
MVP

Re: for each loop in for each + select statement

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;

Community Browser