topic Re: Looking at previous rows until a specific value is found in QlikView

Looking at previous rows until a specific value is found

Wed, 25 Nov 2020 16:16:04 GMT

Hi all

I am looking at using the peek function to return a value based on a specific condition

I have managed to get the peek function to return the correct value when I know how the number of rows, however the number of rows changes

Is there a way to use peek function to look at any number of rows above until a condition is met, then return the value in this row

I am essentially looking where the points scored originated from (Red Zone or Blue Zone)

The ''Pass'' rows differs every time (can be anything from 1 to 50)

Sample dataset below

Re: Looking at previous rows until a specific value is found

adamdavi3s — Mon, 16 Jan 2017 13:17:00 GMT

I'm not really sure what you're trying to achieve here, I think you need to expand on your sample data and make the explanation a little clearer....

1- what value to you want to return

2- where should this value return to? a variable?

3- what defines the order of the data?

But as a loose idea yes you can write loops into a script

Re: Looking at previous rows until a specific value is found

marcus_sommer — Mon, 16 Jan 2017 16:05:47 GMT

It's not possible to include such a condition directly into the peek-function but you could imbed the peek-function within multiple nested if-loops - whereby this would only work if you knows the max. number of iterations and they are not more than 99 (it's a qlikview limitation by nested if-loops) and it would be neither very elegant nor very performant - but by smaller datasets it could be a pragmatism solution.

Another way might be to aggregate the fieldvalues from Category per concat and join them back to your originate table and to count the number of values within the aggregation and using then further string-functions like index(), subfield() and so on to pick the right value - it's probably not very easy and it will need some efforts. In general I mean somehing like this:

table:

load

AnyID, Category, if(AnyID = previous(AnyID), peek('IterNo') + 1, 1) as IterNo

resident Source order by AnyID;

left join (table)

load

AnyID,

concat(Category, ',', Iterno) as CategoryConcat,

concat(IterNo, ',', Iterno) as IternoConcat,

substringcount(concat(Category, ',', Iterno), ',') + 1 as CountConcat

resident table;

Further approach might be to create a separate table with this aggregation and running over them a for-loop to grab each record and within this loop are another for-loop or a while-loop probabaly with some furter if-checks to create some new table or just to join the right value to your originate table.

I hope this will give you some ideas what might be possible to get nearer to your goal.

- Marcus

Re: Looking at previous rows until a specific value is found

MarcoWedel — Mon, 16 Jan 2017 20:22:18 GMT

Hi,

maybe one solution might be:

table1:
LOAD RecNo() as ID,
    Category,
    If(Category like '*Zone', Category, Peek('Zone')) as Zone,
    Ceil(Rand()*100) as SomeValue
INLINE [
    Category
    Red Zone
    Pass
    Pass
    Pass
    Point Scored
    Blue Zone
    Pass
    Pass
    Pass
    Pass
    Pass
    Pass
    Point Scored
];

hope this helps

regards

Marco

Re: Looking at previous rows until a specific value is found

maxgro — Mon, 16 Jan 2017 20:57:09 GMT

table1:

LOAD RecNo() as ID,

Category,

Ceil(Rand()*100) as SomeValue

INLINE [

Category

Red Zone

Pass

Point Scored

Blue Zone

Pass

Point Scored

];

Left Join (table1)

load

ID, Category,

if(Category like 'Point*', Peek(Category)) as Zone

Resident table1

Where not wildmatch(Category, '*Pass*')

Order By ID;