Load Max

Thu, 14 Jul 2011 11:53:11 GMT

Hello

I have the following data set:

Id	Stage 2	Date	Rank
1	Stage 1	01/01/2011	1
2	Stage 2	01/01/2011	2
3	Stage 3	01/01/2011	3
4	Stage 1	01/01/2011	1
5	Stage 2	01/01/2011	2
6	Stage 3	01/01/2011	3
7	Stage 1	01/01/2011	1
8	Stage 2	01/01/2011	2
9	Stage 3	01/01/2011	3

I would like to only rows with the highest 'rank' (Max)

So only Id's 3,6,9 would be loaded,

I think the issue lies with my 'GROUP BY':

LOAD

id,

[Stage 2],

Date,

MAX(Rank) as Rank

FROM

Data\Stage2.qvd (QVD)

GROUP BY id;

If anyone can assist it would be appreciated.

Thanks

Jeremy

Re: Load Max

lironbaram — Thu, 14 Jul 2011 12:11:37 GMT

hei

you have a problem with your Group by

your load script should be something like

LOAD

id,

MAX(Rank) as Rank

FROM

Data\Stage2.qvd (QVD)

GROUP BY id;

Left join

Load

id,

[Stage 2],

Date,

Rank

FROM

Data\Stage2.qvd (QVD)

GROUP BY id;