using Previous with criteria

[samuel_brierley]

Hi all,

is it possible to build criteria into the previous/peek function?

i.e

Field 1, Field 2, Field 3, Calculated Field

A,Z,1,-

B,X,2,-

C,Y,3,-

D,W,4,-

A,Z,5,1

the criteria for the calculated field would be to look upwards and return the value of field 3 where field 1 and 2 are the same.

Thanks guys.

[antoniotiman]

You use Lookup() function

See

Lookup function

[Colin-Albert]

You can use something like this. But in your example data Field 1 and Field2 differ on every row.

IF(Field1 = Field2, Peek(Field3), 'No Match')) as CalculatedField

[sunilkumarqv]

I'm not sure you expecting

something like this

IF(field 1=field 2,Previous(field 3),field 3)

[maxgro]

like this?

if yes this is the script

source:

load * inline [

Field 1, Field 2, Field 3, Calculated Field

A,Z,1,-

B,X,2,-

C,Y,3,-

D,W,4,-

A,Z,5,1

];

final:

NoConcatenate

load

  [Field 1], [Field 2], [Field 3],

  if(peek([Field 1])=[Field 1] and peek([Field 2])=[Field 2], Peek([Field 3]), [Field 3]) as [Calculated Field]

Resident source

order by [Field 1], [Field 2];

DROP Table source;

[samuel_brierley]

Yes exactly like that!!!

one minor change is it possible in your pic rows 3-5 you return 'X' instead of its own value?

if not dont worry.

thanks!!!

[maxgro]

source:

load *, rowno() as id inline [

Field 1, Field 2, Field 3, Calculated Field

A,Z,1,-

B,X,2,-

C,Y,3,-

D,W,4,-

A,Z,5,1

];

Left join (source)

load [Field 1], [Field 2], count([Field 1]) as cnt Resident source

group by [Field 1], [Field 2];

final:

NoConcatenate

load

  [Field 1], [Field 2], [Field 3],

  if(peek([Field 1])=[Field 1] and peek([Field 2])=[Field 2] or cnt>1, alt(peek([Field 3]), [Field 3]), 'x') as [Calculated Field]

Resident source

order by [Field 1], [Field 2], id;

DROP Table source;