topic Re: Use of Previous Function in QlikView

Use of Previous Function

alec1982 — Mon, 11 Nov 2013 16:40:49 GMT

Hi Guys,

I have a table that looks as follow

ID Quarter Current Quarter AA BB CC

1 20133 20132 4.31% 365 365 // Value of BB

1 20134 20132 4.31% 365 522 //365 * 4.31% +365

1 20141 20132 4.31% 365 746 //522 * 4.31+522

1 20142 20132 4.31% 365 1068 //746*4.31% + 746

2 20133 20132 2.22% 245 245 //245

2 20134 20132 2.22% 245 350

2 20141 20132 2.22% 245 501

2 20142 20132 2.22% 245 716

I am trying to add a new column 'CC' where:

If( ID = Previous ID and Quarter - Current Quarter=1,BB,

(Previous (CC )*AA)+CC))

The column that I am trying to build is Column CC which is colored red..

Thxs for all your help.

Re: Use of Previous Function

Mon, 11 Nov 2013 16:48:22 GMT

Try Below:

If( ID = Previous(ID) and Quarter - Current Quarter=1,BB, (Peek(CC)*AA)+Peek(CC)) AS CC

Re: Use of Previous Function

swuehl — Mon, 11 Nov 2013 16:58:21 GMT

Previous(ID) will return NULL for the first line, so you don't get a value back, right?

Try

If( ID <> Previous(ID) and Quarter - [Current Quarter]=1,BB,

(Previous (CC )*AA)+CC))

Re: Use of Previous Function

alec1982 — Mon, 11 Nov 2013 16:58:49 GMT

Hi,

This is working except for the first row where the value should be 365..

Any thoughts?

Thxs,

Alec

Re: Use of Previous Function

alec1982 — Mon, 11 Nov 2013 17:02:19 GMT

The ID needs to Equal the previous ID in order to have the right value when we reach ID 2( I mean the first row of ID 2)..

It is not returning null but it is giving the wrong nr.

Re: Use of Previous Function

alec1982 — Mon, 11 Nov 2013 17:03:45 GMT

Also When i use Previous It gives error that the field doesn't exist while Peek is getting it..

Re: Use of Previous Function

swuehl — Mon, 11 Nov 2013 17:11:26 GMT

Right previous(CC) doesn't work, because CC is not a field in your input table.

This works for me:

Set DecimalSep = '.';

Set ThousandSep = ',';

LOAD *, If( ID <> Peek(ID) and Quarter - [Current Quarter]=1, BB, Peek(CC)*AA + BB) as CC INLINE [

ID, Quarter, Current Quarter, AA, BB , CCTemp

1, 20133, 20132, 4.31%, 365, 365 // Value of BB

1, 20134, 20132, 4.31%, 365, 522 //365 * 4.31% +365

1, 20141, 20132, 4.31%, 365, 746 //522 * 4.31+522

1, 20142, 20132, 4.31%, 365, 1068 //746*4.31% + 746

2, 20133, 20132, 2.22%, 245, 245 //245

2, 20134, 20132, 2.22%, 245, 350

2, 20141, 20132, 2.22%, 245, 501

2, 20142, 20132, 2.22%, 245, 716

];

Though I can't reproduce your CCTemp numbers (how do you calculate 522 from 365* 4.31% +365 ? I do get 380.74

Re: Use of Previous Function

Mon, 11 Nov 2013 17:13:52 GMT

It looks like the calculation was 365 * .431 + 365. It shoudl be 365 * .0431 + 365 which nets the correct 380 I believe.

Re: Use of Previous Function

Mon, 11 Nov 2013 17:15:41 GMT

See attached. This is one possible way to do it via a load script, similar to what others have explained.

Re: Use of Previous Function

alec1982 — Mon, 11 Nov 2013 17:19:11 GMT

Your calculation is correct..I used 0.431 instead of 0.0431..

LOAD *, If( ID <> Peek(ID) and Quarter - [Current Quarter]=1, BB, Peek(CC)*AA + BB) as CC

Multiplying by BB here will get wrong value but if I use Peek(CC) I get the right value..

Any thoughts?

Thxs for all your help!

Re: Re: Use of Previous Function

Mon, 11 Nov 2013 17:27:25 GMT

Hi Brad, Please find the attached file

LOAD *, ROUND(If( Quarter - [Current Quarter]=1 AND ID <> Peek(ID), BB, Peek(CC)*AA +Peek(CC) )) as CC INLINE [

ID, Quarter, Current Quarter, AA, BB

1, 20133, 20132, 4.31%, 365

1, 20134, 20132, 4.31%, 365

1, 20141, 20132, 4.31%, 365

1, 20142, 20132, 4.31%, 365

2, 20133, 20132, 2.22%, 245

2, 20134, 20132, 2.22%, 245

2, 20141, 20132, 2.22%, 245

2, 20142, 20132, 2.22%, 245

];

Re: Use of Previous Function

swuehl — Mon, 11 Nov 2013 17:30:01 GMT

Thoughts about what? I don't really know what you want to calculate, but basically you need to understand the difference between peek() and previous() functions:

"The main difference is that Previous() fetches data from previous input record, whereas Peek() fetches data from previous output record. (Input to the Load statement and output of the Load statement.)

In addition, Peek() can be used to fetch data from other records than the previous and from other tables than the current, if the second and third parameters are used."

If you've understood that and then look at your input data as it enters your load and as it is output from your load, I believe you can apply the correct logic easily.