Join 2 Resident tables together

Tue, 31 Jul 2012 14:32:49 GMT

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;

Re: Join 2 Resident tables together

fosuzuki — Tue, 31 Jul 2012 16:50:42 GMT

Try this:

ListTable:

LOAD PID,

ListID,

DateRec

FROM (ooxml, embedded labels, table is Sheet1);

Data:

LOAD PID,

Min(ListID) AS ListID

Resident ListTable

Group By PID;

Left Join(Data)

LOAD PID,

ListID,

Min(DateRec) AS StartDate

Resident ListTable

Group By PID, ListID;

Drop Table ListTable;

Hope this helps you

Fernando

topic Join 2 Resident tables together in QlikView

Join 2 Resident tables together

Re: Join 2 Resident tables together