topic AW:Re: Working with loaded data in QlikView

Working with loaded data

AbhijitBansode — Sat, 29 Jan 2011 10:12:45 GMT

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.

Working with loaded data

tresB — Sat, 29 Jan 2011 11:45:07 GMT

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

Regards, tresesco

Working with loaded data

AbhijitBansode — Sat, 29 Jan 2011 13:42:59 GMT

Thanks for concern,

Above() function will only work at report level.

my requirement is to achieve this at model level.

AW:Re: Working with loaded data

Sat, 29 Jan 2011 17:01:52 GMT

Hello,

Working with loaded data

tresB — Mon, 31 Jan 2011 05:41:09 GMT

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

Regards, tresesco

AW:Re: Working with loaded data

Mon, 31 Jan 2011 12:00:42 GMT

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

AW:Re: Working with loaded data

AbhijitBansode — Mon, 31 Jan 2011 13:53:16 GMT

thank you. It works