Previous calculation in script

[#1]

Hi All,

I have below data

Week	Amount
1	100
2	200
3	300
4	400
5	500
6	600

I need output like below

Week	Amount	Previous Week 1	Previous Week 2	Previous Week 3	Previous Week 4
1	100
2	200	100
3	300	200	100
4	400	300	200	100
5	500	400	300	200	100
6	600	500	400	300	200

Note : I need this in script only

[agomes1971]

Hi,

please read this

The Crosstable Load

HTH

André Gomes

[Clever_Anjos]

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

[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

[sunny_talwar]

We think a like

[Clever_Anjos]

Yes

[#7 · Accepted solution]

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]

It should perform even better with a bigger bunch of data