Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

adamjiawen
New Contributor II

Sum a value influenced by the same column values

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!!!

1 Solution

Accepted Solutions

Re: Sum a value influenced by the same column values

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;

5 Replies
Not applicable

Re: Sum a value influenced by the same column values

PFA

adamjiawen
New Contributor II

Re: Sum a value influenced by the same column values

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.

Not applicable

Re: Sum a value influenced by the same column values

can you mark this thread as Answered?

Re: Sum a value influenced by the same column values

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;

adamjiawen
New Contributor II

Re: Sum a value influenced by the same column values

Thank you guys ,Ramkumar,Manish , thank you very much for your help.

Community Browser