How do i make a self-join

[Anonymous]

I need to join a table to it self.

In my table i would have these records:

Order Company Invoice

1 0 99

1 1 55

The result should look like this:

Order Company Invoice RelInvoice

1 0 99 -

1 1 55 99

Note, that i only want a value in RelInvoice, if Company is different from 0.

Is it possible ?

[Anonymous]

Hi HintzeXyCon,

So if Company changes from 0 to something else you want to load the Invoice from the row above as RelInvoice?

In that case you can use the peek() function.

For example:

if(peek('Company')<>0,peek('Invoice')) as RelInvoice

[Anonymous]

Hi,

I forgot to mention that the "Order" has to match..

[yblake]

read data orderd by Order asc, then :

if(Order<>peek('Order'),Invoice,if peek('Company')<>0,peek('Invoice'))) as RelInvoice

[#5]

Hi,

Please, take a look into attached example.

I hope it will help you to find the proper solution.

Best regards,

JTPro