topic How do i make a self-join in QlikView

How do i make a self-join

Anonymous — Thu, 23 Apr 2009 14:39:17 GMT

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 — Thu, 23 Apr 2009 15:07:46 GMT

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 — Mon, 27 Apr 2009 16:29:33 GMT

Hi,

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

yblake — Mon, 27 Apr 2009 16:36:05 GMT

read data orderd by Order asc, then :

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

Mon, 27 Apr 2009 16:53:19 GMT

Hi,

Please, take a look into attached example.

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

Best regards,

JTPro