Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
Output:
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).
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;
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
];