Looking at previous rows until a specific value is found

[#1]

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

Category

Red Zone

Pass

Pass

Pass

Point Scored

Blue Zone

Pass

Pass

Pass

Pass

Pass

Pass

Point Scored

[adamdavi3s]

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

[marcus_sommer]

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

[MarcoWedel]

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

[maxgro]

table1:

LOAD RecNo() as ID,

     Category,

     Ceil(Rand()*100) as SomeValue

INLINE [

    Category

    Red Zone

    Pass

    Pass

    Pass

    Point Scored

    Blue Zone

    Pass

    Pass

    Pass

    Pass

    Pass

    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;