Joining two tables with mutliples

[bazzaonline]

Help!!

I want to join to tables in the script but one has multiple fields per row.. (hope that makes sense),  For example

Table 1

Ref     Status

1          Open

2          Closed

3          Open

4          Closed

Table 2

Ref        Position     Date

1            Invoiced    01/01/2015

1            Paid          05/01/2015

2            Invoiced    02/01/2015

3            Invoiced    02/01/2015

3            Paid          06/01/2015

I want to create the below table, does anyone know a way?

Ref     Status          Invoiced         Paid

1          Open          01/01/2015     05/01/2015

2          Closed       02/01/2015      02/01/2015

3          Open          02/01/2015     06/01/2015

4          Closed

Thanks Paul

[sunny_talwar]

May be this:

Table2:

LOAD * Inline [

Ref, Position, Date

1,      Invoiced,  01/01/2015

1,      Paid,      05/01/2015

2,      Invoiced,  02/01/2015

3,      Invoiced,  02/01/2015

3,      Paid,      06/01/2015

];

Join (Table2)

LOAD * Inline [

Ref,    Status

1,      Open

2,      Closed

3,      Open

4,      Closed

];

FinalTable:

LOAD Ref,

  Date as Invoiced

Resident Table2

Where Position = 'Invoiced';

Join (FinalTable)

LOAD Ref,

  Date as Paid

Resident Table2

Where Position = 'Paid' or Status = 'Closed';

DROP Table Table2;