Join or what?

[#1]

Hi all!

I've the following situation:

TABLE1

Field1     Field2     Field3

   F1          F1          R1

  F2          F2              

                R1

I would like to fill Field1 with the value of Field2 when Field2 is equal to Field3.

In this case:

TABLE1

Field1     Field2     Field3

  F1          F1          R1

  F2          F2              

  F1          R1

Can you please help me?

Thanks in advance,

Alfonso.

[CELAMBARASAN]

Try like this

Load

     If(Field2=Field3,Field2,Field1) AS Field1,

     Field2,

     Field3

From...

[#3]

It's not exactly what I need...

As you can see I've to match fields from different rows.

In the example the value in Field2 on the third row is equal to the one in the Field3 on the first row, so I've to fill Field1 of the third row with Field2 of the first row.

I hope it was clear.

Best regards,

Alfonso.

[giakoum]

Then you must use Previous function :

previous(expression )

Returns the value of expression using data from the previous input record that was not discarded due to a where clause. In the first record of an internal table the function will return NULL. The previous function may be nested in order to access records further back. Data are fetched directly from the input source, making it possible to refer also to fields which have not been loaded into QlikView, i.e. even if they have not been stored in its associative database.

or Peek :

peek(fieldname [ , row [ , tablename ] ] )

Returns the contents of the fieldname in the record specified by row in the internal table tablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.

If no row is stated, -1 is assumed.

Tablename is a table label, see Table Labels, without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.

[#5]

Thanks for your help Ioannis but these functions seem to access to the previous record only.

Is there a function that could let me search a value on the entire table and return the row number (then I could use the PEEK function)?

[giakoum]

lookup(fieldname, matchfieldname, matchfieldvalue [, tablename])

Returns the value of fieldname corresponding to the first occurrence of the value matchfieldvalue in the field matchfieldname.

Fieldname, matchfieldname and tablename must be given as strings (e.g. quoted literals).

The search order is load order unless the table is the result of complex operations such as joins, in which case the order is not well defined.

Both fieldname and matchfieldname must be fields in the same table, specified by tablename. If tablename is omitted the current table is assumed.

If no match is found, null is returned.

lookup(F1, F2, F3) or something like that...