Hi,
I have a table with the folowing structure
PID ListID DateRec
1 1 01/04/10
1 1 05/02/10
1 1 08/09/10
1 2 04/06/11
1 2 09/06/11
2 2 07/06/12
2 2 06/07/12
3 1 08/09/10
3 2 10/01/10
I need to work out if a PID has a ListID of then 1, if so I need the MAX date, if not then I need to check if the PID had a ListID of 2 and if so so I need the MIN Date
So in the example above I would want a result of:
PID StartDate
1 05/02/10
2 06/07/12
3 08/09/10
Not sure on the best way at doing this. Have tried different options. My idea was something like below:
ListTable:
LOAD PID,
ListID,
DateRec
FROM
(ooxml, embedded labels, table is Sheet1);
Hope this makes sense. In the code above, I get an error in Join part, it doesn't seem to like Table2.
Many thanks in advance.
Cheers
Simon
Table1:
LOAD PID,
ListID,
Date(MAX(DateRec)) as StartDate
RESIDENT ListTable
WHERE ListID = 1
GROUP BY PID, ListID;
Table 2:
LOAD PID,
ListID,
Date(MIN(DateRec)) as StartDate
RESIDENT ListTable
GROUP BY PID, ListID;
Qualify *;
Unqualify PID;
StartTable
LOAD *
Resident Table1;
Drop Table Table1;
JOIN (StartTable)
LOAD *
Resident Table2;
Drop Table Table2;
FinalTable:
Load PID,
Date(MIN(StartDate)) as StartDate
RESIDENT StartTable
GROUP BY PID;
