How can I get the second table from the resident table

[D19PAL]

Hi Guys,

How can I get the bottom table from the top resident table?

Thanks

Top table Resident

Name	Date 1	order date	Id	Before_or_ after
John	01/01/2010	01/01/2010	1	Before
John	01/01/2010	01/01/2010	1	Before
John	01/01/2010	01/01/2010	1	Before
John	01/01/2010	05/01/2010	2	After
John	01/01/2010	05/01/2010	2	After
John	01/01/2010	05/01/2010	2	After
Paul	01/01/2010	01/01/2010	3	Before
Paul	01/01/2010	01/01/2010	3	Before
Paul	01/01/2010	01/01/2010	3	Before
Paul	01/01/2010	05/01/2010	4	After
Paul	01/01/2010	05/01/2010	4	After
Paul	01/01/2010	05/01/2010	4	After
What I need from the resident table In the table below.
Name	Id	Before_or After
John	1	Before
John	2	After
Paul	3	Before
Paul	4	After

[Saravanan_Desingh]

Try this,

tab1:
LOAD * INLINE [
    Name, Date 1, order date, Id, Before_or_ after
    John, 01/01/2010, 01/01/2010, 1, Before
    John, 01/01/2010, 01/01/2010, 1, Before
    John, 01/01/2010, 01/01/2010, 1, Before
    John, 01/01/2010, 05/01/2010, 2, After
    John, 01/01/2010, 05/01/2010, 2, After
    John, 01/01/2010, 05/01/2010, 2, After
    Paul, 01/01/2010, 01/01/2010, 3, Before
    Paul, 01/01/2010, 01/01/2010, 3, Before
    Paul, 01/01/2010, 01/01/2010, 3, Before
    Paul, 01/01/2010, 05/01/2010, 4, After
    Paul, 01/01/2010, 05/01/2010, 4, After
    Paul, 01/01/2010, 05/01/2010, 4, After
];

tab2:
NoConcatenate
LOAD DISTINCT Name, Id, [Before_or_ after]
Resident tab1;

Drop Table tab1;

[Saravanan_Desingh]

Output:

[D19PAL]

What if I wanted to join the new bottom table back into the resident table (That is initially the main thing I want. I just asked in steps).

[Saravanan_Desingh]

Try something like this,

tab1:
LOAD * INLINE [
    Name, Date 1, order date, Id, Before_or_ after
    John, 01/01/2010, 01/01/2010, 1, Before
    John, 01/01/2010, 01/01/2010, 1, Before
    John, 01/01/2010, 01/01/2010, 1, Before
    John, 01/01/2010, 05/01/2010, 2, After
    John, 01/01/2010, 05/01/2010, 2, After
    John, 01/01/2010, 05/01/2010, 2, After
    Paul, 01/01/2010, 01/01/2010, 3, Before
    Paul, 01/01/2010, 01/01/2010, 3, Before
    Paul, 01/01/2010, 01/01/2010, 3, Before
    Paul, 01/01/2010, 05/01/2010, 4, After
    Paul, 01/01/2010, 05/01/2010, 4, After
    Paul, 01/01/2010, 05/01/2010, 4, After
];

Concatenate(tab1)
LOAD DISTINCT Name, Id, [Before_or_ after]
Resident tab1;

[manoranjan_d]

Hi D19PAl

Instead of resident load you can go with preceding load. 

Please clearly explain your requirement for join.

tab1:
Load
Distinct *;

LOAD * INLINE [
Name, Date 1, order date, Id, Before_or_ after
John, 01/01/2010, 01/01/2010, 1, Before
John, 01/01/2010, 01/01/2010, 1, Before
John, 01/01/2010, 01/01/2010, 1, Before
John, 01/01/2010, 05/01/2010, 2, After
John, 01/01/2010, 05/01/2010, 2, After
John, 01/01/2010, 05/01/2010, 2, After
Paul, 01/01/2010, 01/01/2010, 3, Before
Paul, 01/01/2010, 01/01/2010, 3, Before
Paul, 01/01/2010, 01/01/2010, 3, Before
Paul, 01/01/2010, 05/01/2010, 4, After
Paul, 01/01/2010, 05/01/2010, 4, After
Paul, 01/01/2010, 05/01/2010, 4, After
];