Working with loaded data

[AbhijitBansode]

Hi ,

I'm getting below table by performing some calculations on loaded data:

ProductNo	Term	TernAvg
1	20091stTerm	10000
1	20092ndTerm	2000
1	201011stterm	3000
1	20102ndTerm	500
2	20091stTerm	15000
2	20092ndTerm	1300
2	201011stterm	1500
2	20102ndTerm	1400

Now my requirement is to add one more column viz., Prev_termAvg to this table which will calculate the previous term average of the term for each product.

ProductNo	Term	TernAvg	Prev_termAvg
1	20091stTerm	10000	0
1	20092ndTerm	2000	1000
1	201011stterm	3000	2000
1	20102ndTerm	500	3000
2	20091stTerm	15000	0
2	20092ndTerm	1300	15000
2	201011stterm	1500	1300
2	20102ndTerm	1400	1500

Here , Prev_termAvg of 20091stTerm for ProductNo 1 is 0, because productNo 1 is starting from 20091stTerm , lly for ProductNo 2

Please let me know how to achieve this operation at QlikView script level.

[tresesco]

Use Above() function. Like this: Above("TermAvg").

Regards, tresesco

[AbhijitBansode]

Thanks for concern,

Above() function will only work at report level.

my requirement is to achieve this at model level.

[#4]

Hello,

see also:

http://community.qlik.com/forums/p/40143/158233.aspx#158233

RR

[tresesco]

At script you can achieve this by using Previous() OR Peek() function. Try to use it with Order By Clause (if required).

Regards, tresesco

[#6 · Accepted solution]

Hello cabhiji,

I loaded your raw data inline and tried this:

NoConcatenate
LOAD
 *,
 IF(ProductNo = Previous(ProductNo),
 Previous(TernAvg),
 0) AS Prev_termAvg
Resident RawData;

Regards, Roland

[AbhijitBansode]

thank you. It works