Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Previous calculation in script

Hi All,

I have below data

 

WeekAmount
1100
2200
3300
4400
5500
6600

I need output like below

  

WeekAmountPrevious Week 1Previous Week 2Previous Week 3Previous Week 4
1100
2200100
3300200100
4400300200100
5500400300200100
6600500400300200

Note : I need this in script only

1 Solution

Accepted Solutions
Not applicable
Author

I got it to work like below, is it good way?

Tab:

LOAD * Inline [

Week    Amount

1    100

2    200

3    300

4    400

5    500

6    600 ] (delimiter is '\t');

Data:

NoConcatenate

LOAD *,

     peek(Amount,-1) as [Previous week 1],

     peek(Amount,-2) as [Previous week 2],

     peek(Amount,-3) as [Previous week 3],

     peek(Amount,-4) as [Previous week 4]

Resident Tab

Order by Week asc;

DROP Table Tab;


View solution in original post

8 Replies
agomes1971
Specialist II
Specialist II

Hi,

please read this

The Crosstable Load

HTH

André Gomes

Clever_Anjos
Employee
Employee

Table:

LOAD Week,

     Amount

FROM

[https://community.qlik.com/thread/230270]

(html, codepage is 1252, embedded labels, table is @1);

For i = 1 to 4

  left join(Table)

  LOAD

  Week + $(i) as Week,

  Amount as   [Previous Week $(i)]

  Resident Table;

Next

Capturar.PNG

sunny_talwar

You can try this:

Table:

LOAD * INLINE [

    Week, Amount

    1, 100

    2, 200

    3, 300

    4, 400

    5, 500

    6, 600

];

FOR i = 1 to 4

  Left Join(Table)

  LOAD Week + $(i) as Week,

  Amount as [Previous Week $(i)]

  Resident Table;

NEXT


Capture.PNG

sunny_talwar

We think a like

Clever_Anjos
Employee
Employee

Yes

Not applicable
Author

I got it to work like below, is it good way?

Tab:

LOAD * Inline [

Week    Amount

1    100

2    200

3    300

4    400

5    500

6    600 ] (delimiter is '\t');

Data:

NoConcatenate

LOAD *,

     peek(Amount,-1) as [Previous week 1],

     peek(Amount,-2) as [Previous week 2],

     peek(Amount,-3) as [Previous week 3],

     peek(Amount,-4) as [Previous week 4]

Resident Tab

Order by Week asc;

DROP Table Tab;


sunny_talwar

Looks good as well

Clever_Anjos
Employee
Employee

It should perform even better with a bigger bunch of data