ALTERNATIVE OF PEEK() FUNCTION IN SQL

[JMAROUF]

Hi,

I'am looking to increment the current calculated value as a field in SQL, that is possible in Qlik with peek() function, the goal is to create and increment the state field in each investor or roject activationp state, here is my DATA:

PROJECT|INVESTOR|DATE  |IS_ACTIF

|:-----|:------:|:----:|-------:|

P1     |I1      |010124|1

P1     |I1      |020124|1

P1     |I1      |030124|0

P1     |I1      |040124|1

P1     |I2      |050124|1

P1     |I2      |060124|0

Here is the desired result:

PROJECT|INVESTOR|DATE  |IS_ACTIF|STATE|

|:-----|:------:|:----:|--------|----:|

P1     |I1      |010124|1       | 1    

P1     |I1      |020124|1       | 1

P1     |I1      |030124|0       | 2

P1     |I1      |030124|1       | 3

P1     |I2      |050124|1       | 1

P1     |I2      |060124|0       | 2

How cana'I achieve this with SQL please?

**WITH QLIK:**

```

STATE:

NoConcatenate

LOAD DISTINCT 

            PROJECT,

    INVESTOR,

            DATE,

    IS_ACTIF,   

    IF(RowNo()=1 OR Previous(PROJECT&INVESTOR)<>PROJECT&INVESTOR,1,

                        IF(  IS_ACTIF=Previous(IS_ACTIF)

               ,peek(STATE),peek(STATE)+1) )                                                AS STATE

RESIDENT DATA ORDER BY PROJECT, INVESTOR,DATE;

```

[vinieme12]

you can use  Lead() or Lag() function 

[JMAROUF]

Thanks @vinieme12 , with lag it's not possible to use the actual calculated field as a value, I tired but it doesn't work.

[vinieme12]

It is possible; maybe you need to adjust your window aggregation or maybe you need another level of nesting

Since this is SQL related forum I suggest you post this on an appropriate Sql forum of the database you are working or stackoverflow

[JMAROUF · Accepted solution]

Thanks, this have worked for me:

SELECT PROJECT, 
       INVESTOR, 
       "DATE", 
       IS_ACTIF, 
       SUM(ABS(is_actif-lagged_actif)) OVER (PARTITION BY PROJECT, INVESTOR ORDER BY row_id) AS "STATE"
FROM 
    (
     SELECT *, ROW_NUMBER() OVER(PARTITION BY PROJECT, INVESTOR) as row_id, LAG(is_actif,1, 0) OVER(PARTITION BY PROJECT, INVESTOR) as lagged_actif
     FROM TEST_SO
     ) a