topic Re: Previous calculation in script in QlikView

Previous calculation in script

Wed, 24 Aug 2016 17:04:54 GMT

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

Re: Previous calculation in script

agomes1971 — Wed, 24 Aug 2016 17:09:48 GMT

Hi,

please read this

The Crosstable Load

HTH

André Gomes

Re: Previous calculation in script

Clever_Anjos — Wed, 24 Aug 2016 17:11:01 GMT

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

Re: Previous calculation in script

sunny_talwar — Wed, 24 Aug 2016 17:14:45 GMT

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

Re: Previous calculation in script

sunny_talwar — Wed, 24 Aug 2016 17:15:19 GMT

We think a like

Re: Previous calculation in script

Clever_Anjos — Wed, 24 Aug 2016 17:15:48 GMT

Yes

Re: Previous calculation in script

Wed, 24 Aug 2016 17:23:40 GMT

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;

Re: Previous calculation in script

sunny_talwar — Wed, 24 Aug 2016 17:24:35 GMT

Looks good as well

Re: Previous calculation in script

Clever_Anjos — Wed, 24 Aug 2016 17:29:55 GMT

It should perform even better with a bigger bunch of data