Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone, During Learning to use Qlikview ,I've got a problem ,Hope for everyone's Help Thanks
First,I've a table like this without column D
----------------------------------------------
A B C
1 20 10
1 30 20
1 40 30
2 10 2
2 15 4
2 20 6
3 10 3
3 20 6
3 25 9
-----------------------------------------------
Then I want to handle the table into the following table,and there's rule in handling the table that is
-----------------------------------------------
A B C D
1 20 10 (B-C-D) 10 A new start
1 30 20 (B-C-D) 30-20-10=0 the D in this row is all the D above this row
1 39 30 (B-C-D) 39-30-10-0= -1 the D in this row is all the D above this row
2 10 2 8
2 15 4 3
2 20 6 3
3 10 3 7
3 20 6 7
3 25 9 2
---------------------------------------------------
I handle the table in scripts ,So Hope for scripts in details to achieve this function ,Thank you very much for your help!!!
TEMP:
Load * Inline
[
A, B, C
1 ,20, 10
1 ,30, 20
1 ,39, 30
2 ,10, 2
2 ,15 , 4
2 ,20, 6
3 ,10, 3
3 ,20, 6
3 ,25, 9
];
NoConcatenate
FINAL:
Load
A,
B,
C,
B-C as D1,
IF(A<>Previous(A), B-C, B-C-Peek(D1)) as D
Resident TEMP Order By A,B,C;
Drop Field D1;
Drop table TEMP;
PFA
Great,but I've made a mistake in the first post,What I really mean is to get total of the Ds of the same A above the current row reduced.
You have used the rowno which means it only works in the first line ,for the other same A like 2、3 it doesn't work.
can you mark this thread as Answered?
TEMP:
Load * Inline
[
A, B, C
1 ,20, 10
1 ,30, 20
1 ,39, 30
2 ,10, 2
2 ,15 , 4
2 ,20, 6
3 ,10, 3
3 ,20, 6
3 ,25, 9
];
NoConcatenate
FINAL:
Load
A,
B,
C,
B-C as D1,
IF(A<>Previous(A), B-C, B-C-Peek(D1)) as D
Resident TEMP Order By A,B,C;
Drop Field D1;
Drop table TEMP;
Thank you guys ,Ramkumar,Manish , thank you very much for your help.