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 hoe to achieve this operation at QlikView script level.

[#2 · 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