Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Need help on how i can achieve this in Qlikview. VCHSEQ is the column that will only populate 1 or 2 base on the condition of the previous row. I have tried to use peek and for loop but no success so far. Please refer to the attachment for more details. Any help is appreciated!
RRN | XVCH7 | VCHSEQ | This Row RRN minus Previous Row RRN |
668254 | 100000 | 1 | 0 |
668255 | 100000 | 1 | 1 |
668256 | 100000 | 1 | 1 |
668257 | 100000 | 1 | 1 |
668258 | 100000 | 1 | 1 |
668259 | 100000 | 1 | 1 |
668260 | 100000 | 1 | 1 |
668261 | 100000 | 1 | 1 |
668262 | 100000 | 1 | 1 |
668263 | 100000 | 1 | 1 |
668264 | 100000 | 1 | 1 |
668265 | 100000 | 1 | 1 |
668266 | 100000 | 1 | 1 |
668267 | 100000 | 1 | 1 |
1 | 100001 | 1 | -668266 |
2 | 100001 | 1 | 1 |
3 | 100001 | 1 | 1 |
668268 | 100001 | 2 | 668265 |
668269 | 100001 | 2 | 1 |
668270 | 100001 | 2 | 1 |
668271 | 100001 | 2 | 1 |
668272 | 100002 | 2 | 1 |
668273 | 100002 | 2 | 1 |
668274 | 100002 | 2 | 1 |
668275 | 100002 | 2 | 1 |
4 | 100003 | 1 | -668271 |
5 | 100003 | 1 | 1 |
6 | 100003 | 1 | 1 |
668276 | 100003 | 2 | 668270 |
668277 | 100003 | 2 | 1 |
668278 | 100003 | 2 | 1 |
668279 | 100003 | 2 | 1 |
668280 | 100003 | 2 | 1 |
LOAD
RRN,
XVCH7,
Alt(If(RRN-Previous(RRN)<>1,
If(Previous(XVCH7)<>XVCH7,1,2),Peek(VCHSEQ)),0) as VCHSEQ
FROM
....
LOAD
RRN,
XVCH7,
Alt(If(RRN-Previous(RRN)<>1,
If(Previous(XVCH7)<>XVCH7,1,2),Peek(VCHSEQ)),0) as VCHSEQ
FROM
....
Work brilliantly for me. Thanks!