topic Previous() in QlikView

Previous()

Thu, 18 Sep 2014 10:46:21 GMT

I have a piece of script that looks at the values of a table, these values are completely random and have no set sequecne.

For example

Col A Col B Line_Number

45456464 8 1

45456464 45 2

45456464 21 3

102154 5 1

102154 3 2

324549 8 2

7878454 9 1

7878454 2 2

So the line number works fine when there is multiple occurances it resets to one which is great, however if you look at Col A value 324549 the line_number goes to 2.

How can I get the Line_number to reset to 1 in this instance while ensuring all other line_numbers are calculated as per above.

if(Previous(col_A)=col_A,numsum(peek('Line_Number'),1),1) As Line_Number

Thanks,

Aidan

Re: Previous()

anbu1984 — Thu, 18 Sep 2014 10:55:59 GMT

Your code works correctly.

Load *,if(Previous(col_A)=col_A,numsum(peek('Line_Number'),1),1) As Line_Number1 Inline [

col_A,col_B,Line_Number

45456464,8,1

45456464,45,2

45456464,21,3

102154,5,1

102154,3,2

324549,8,2

7878454,9,1

7878454,2,2 ];

Re: Previous()

MK_QSL — Thu, 18 Sep 2014 10:59:44 GMT

Temp:

Load * Inline

[

Col A, Col B

45456464, 8

45456464, 45

45456464, 21

102154, 5

102154, 3

324549, 8

7878454, 9

7878454, 2

];

Final:

Load

[Col A],

[Col B],

IF([Col A] = Previous([Col A]), RangeSum(Peek('Line_Number'),1),1) as Line_Number

Resident Temp

Order By [Col A], [Col B];

Drop Table Temp;

Re: Previous()

rubenmarin — Thu, 18 Sep 2014 11:00:07 GMT

Try using peek.

Maybe, if you have a 'where' or 'if' condition, previous will take the descarted register from source table, so it counts there is one previous register where Col_A='324549'.

if(Peek('col_A')=col_A,numsum(peek('Line_Number'),1),1) As Line_Number

Re: Previous()

tresB — Thu, 18 Sep 2014 11:04:15 GMT

Much simpler:

Load

AutoNumber(RecNo(), ColA) as LineNo

Re: Previous()

maxgro — Thu, 18 Sep 2014 11:16:27 GMT

load

...,

if([Col A]=peek([Col A]), peek(Line_Number)+1, 1 ) as Line_Number

Resident ......

Order By [Col A], [Col B];

Re: Previous()

Thu, 18 Sep 2014 14:44:47 GMT

Thank you Massimo simple soultion in the end:)

Re: Previous()

Thu, 18 Sep 2014 14:45:52 GMT

Thanks tresesco worked great