Can I use the results of a calculated field within the same calculated field?

Question

I am working on converting some QlikView scripts into Redshift scripts. My QlikView script has a calculated field that uses the calculated result from the previous row to get the result of the current row.

It is checking for a match between the previous ID and current ID. If they don't match, the value is one. If they do match, it's the previous result + 1 as the result.

For example, this is what I'm expecting
| ID | Timestamp | Order|
| --- | --- | --- |
| 1001 | 2023-10-01 08:00:00 | 1 |
| 1002 | 2023-10-01 08:00:00 | 1 |
| 1002 | 2023-10-01 08:30:00 | 2 |
| 1002 | 2023-10-01 09:00:00 | 3 |
| 1003 | 2023-10-01 08:00:00 | 1 |
| 1003 | 2023-10-01 08:15:00 | 2 |
| 1003 | 2023-10-01 08:30:00 | 3 |
| 1003 | 2023-10-01 08:45:00 | 4 |
| 1004 | 2023-10-01 08:30:00 | 1 |

I know that i can use lag() to view the previous ID

`CASE 
    WHEN ID != lag(ID,1) over (order by ID, Timestamp) THEN 1
    ELSE 0
END as Order
`

But I am not sure what how to get the previous value of Order within my ELSE statement.

Is it possible to do it this way? Do I need to do it another way?

Any advice or help would be great.

Accepted Answer

I was able to achieve the results you expected with a different SQL. Kindly see if this will help your use case.

```
create table tst_ordr
(    ID NUMERIC,
    tmstmp TIMESTAMP
);

INSERT INTO tst_ordr values 
(1001,'2023-10-01 08:00:00'),
(1002,'2023-10-01 08:00:00'),
(1002,'2023-10-01 08:30:00'),
(1002,'2023-10-01 09:00:00'),
(1003,'2023-10-01 08:00:00'),
(1003,'2023-10-01 08:15:00'),
(1003,'2023-10-01 08:30:00'),
(1003,'2023-10-01 08:45:00'),
(1004,'2023-10-01 08:30:00')
;

select *,
    row_number() 
        over (partition by id 
              order by ID, tmstmp)
     as "Order"
from tst_ordr
order by 1,3;
```

![results](/media/postImages/original/IMgntO1m5PT-29NpNBsTkI4g)

ID	Timestamp	Order
1001	2023-10-01 08:00:00	1
1002	2023-10-01 08:00:00	1
1002	2023-10-01 08:30:00	2
1002	2023-10-01 09:00:00	3
1003	2023-10-01 08:00:00	1
1003	2023-10-01 08:15:00	2
1003	2023-10-01 08:30:00	3
1003	2023-10-01 08:45:00	4
1004	2023-10-01 08:30:00	1

Can I use the results of a calculated field within the same calculated field?

Relevant content