Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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
We think a like
Yes
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;
Looks good as well
It should perform even better with a bigger bunch of data