joining on same field in table

[#1]

Material Number	Reference Number	Amount	DocumentNumber	Movement Type
500	987654	20	123456	303
500	-	19	987654	305

i have the following table. Whereby i wish to join the ReferenceKey onto the DocumentKey to form a Unique row.

is this possible to do? i realize i can change the labels for Amount such as Order in Order Out and drop the MovementType but cant seem to find solve the join.

any help would be grealty appreciated.

the row i am looking for in the end result would look something like this.

Order In	Order Out	Article ID	Document Key
19	20	500	987654

Best

Brad

[marcus_sommer]

Try something like this

table:

Load

     [Material Number] as [Article ID],

     [Reference Number] as [Documemt Key],

     [Amount] as [Order Out]

From xyz where len([Reference Number]) >=1;

join (Table)

Load

     [Reference Number] as [Documemt Key],

     [Amount] as [Order In]

From xyz where len([Reference Number]) =0;

whereby in your real case your where-clause to identify what's what could be more complex.

- Marcus