Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Total cumulative and in the opposite direction, the cursor and the temporary table

Total cumulative and in the opposite direction, the cursor and the temporary table

Hello, colleagues.

Please help.

There is a table with the original data inputted from the cumulative total for the quarter (in Excel).

See Table 1 below.

That is, for example, Ref1 (01.07.2011) = Ref1 (01.04.2011) + X. Contrariwise: Ref1 (01.04.2011) = Ref1 (01.07.2011) - X.

And so X = Ref1 (01.07.2011) - Ref1 (01.04.2011)

Alike, calculated Ref2, Ref3.

I have the task: for each line in table 1 to calculate X and build table 2.

As I understand it task, i need cursor (like as SQL) and temporary table, but I can't to create a script

Do you have any thoughts on this issue?

Table 1

Q_ref

Date_ref

ZG

Ref1

Ref2

Ref3

1

  1. 01.01.2011

Automag ltd

10

10

30

2

  1. 01.04.2011

Automag ltd

25

20

50

3

  1. 01.07.2011

Automag ltd

30

30

60

4

  1. 01.10.2011

Automag ltd

45

40

70

1

  1. 01.01.2012

Automag ltd

10

10

30

2

  1. 01.04.2012

Automag ltd

10

20

50

3

  1. 01.07.2012

Automag ltd

10

30

60

4

  1. 01.10.2012

Automag ltd

25

40

65

1

  1. 01.01.2013

Automag ltd

10

10

30

2

  1. 01.04.2013

Automag ltd

35

10

50

3

  1. 01.07.2013

Automag ltd

45

10

60

4

  1. 01.10.2013

Automag ltd

55

30

70

1

  1. 01.01.2011

Craig gmbh

10

10

30

2

  1. 01.04.2011

Craig gmbh

20

20

50

3

  1. 01.07.2011

Craig gmbh

30

30

60

4

  1. 01.10.2011

Craig gmbh

40

40

70

1

  1. 01.01.2012

Craig gmbh

10

10

30

2

  1. 01.04.2012

Craig gmbh

10

20

50

3

  1. 01.07.2012

Craig gmbh

10

30

60

4

  1. 01.10.2012

Craig gmbh

10

40

70

Table 2

Q_ref

Date_ref

ZG

Ref1

Х1

Ref2

Х2

Ref2

Х3

1

  1. 01.01.2011

Automag ltd

10

10

2

  1. 01.04.2011

Automag ltd

25

15

3

  1. 01.07.2011

Automag ltd

30

5

4

  1. 01.10.2011

Automag ltd

45

15

1

  1. 01.01.2012

Automag ltd

10

10

2

  1. 01.04.2012

Automag ltd

10

0

3

  1. 01.07.2012

Automag ltd

10

0

4

  1. 01.10.2012

Automag ltd

25

15

1

  1. 01.01.2013

Automag ltd

10

10

2

  1. 01.04.2013

Automag ltd

35

25

3

  1. 01.07.2013

Automag ltd

45

10

4

  1. 01.10.2013

Automag ltd

55

10

1

  1. 01.01.2011

Craig gmbh

10

10

2

  1. 01.04.2011

Craig gmbh

20

10

3

  1. 01.07.2011

Craig gmbh

30

10

4

  1. 01.10.2011

Craig gmbh

40

10

1

  1. 01.01.2012

Craig gmbh

10

10

2

  1. 01.04.2012

Craig gmbh

10

0

3

  1. 01.07.2012

Craig gmbh

10

0

4

  1. 01.10.2012

Craig gmbh

10

0

Thanks!

1 Solution

Accepted Solutions

Re: Total cumulative and in the opposite direction, the cursor and the temporary table

See attached example.


talk is cheap, supply exceeds demand
7 Replies

Re: Total cumulative and in the opposite direction, the cursor and the temporary table

That is, for example, Ref1 (01.07.2011) = Ref1 (01.04.2011) + X. Contrariwise: Ref1 (01.04.2011) = Ref1 (01.07.2011) - X.

And so X = Ref1 (01.07.2011) - Ref1 (01.04.2011)

Ref1 (01.07.2011) = 30

Ref1 (01.04.2011)= 25

X = 30 - 25 = 5.


In table 2 X is 10 for Q_ref value 1.


What gives?



talk is cheap, supply exceeds demand
Not applicable

Re: Total cumulative and in the opposite direction, the cursor and the temporary table

I'm so sory.

in the first quarter the value is not calculated and is equal to Х

In table 2 X is 10 for Q_ref value 1 - correct

10х Gysbert for the important note/

Re: Total cumulative and in the opposite direction, the cursor and the temporary table

I still don't understand how you calculate the X values. Please give us examples for the first four rows of table1.


talk is cheap, supply exceeds demand
Not applicable

Re: Total cumulative and in the opposite direction, the cursor and the temporary table

a common example in Table

Q_ref

Date_ref

ZG

Ref1

Х

1

  1. 01.01.2011

Automag ltd

10

=10

2

  1. 01.04.2011

Automag ltd

25

= Ref1 (01.04.2011) - Ref1 (01.01.2011) = 15

3

  1. 01.07.2011

Automag ltd

30

= Ref1 (01.07.2011) - Ref1 (01.04.2011) = 5

4

  1. 01.10.2011

Automag ltd

45

= Ref1 (01.10.2011) - Ref1 (01.07.2011) = 15

Not applicable

Re: Total cumulative and in the opposite direction, the cursor and the temporary table

in the test example, I use the expression

if(ceil(month(Date_ref)/3) = 1 and 1=1, Ref1, peek('Ref1',-1) - Ref1) as ref_qty

see attach.

in modulus are calculated correctly, but changes sign.


!Do you have any thoughts on this issue?


Re: Total cumulative and in the opposite direction, the cursor and the temporary table

See attached example.


talk is cheap, supply exceeds demand
Not applicable

Re: Total cumulative and in the opposite direction, the cursor and the temporary table

Very thanks.

This work.

You are my hero Smiley Happy

Community Browser