Dropping Tables in Load Statement. Find highest Value.

[bnelson111]

Everyone,

I have been attempting to load tables only keep the highest value then drop all other cells. Below is the load statements.To help explain the table out is below after the load. Why is the there for example P45844 to last id's when i want to show only the highest which would be 18527346? Thanks for help, i know this maybe badly explained.

QUALIFY *;

:

LOAD SpecCode as toollevel,

ToolStatus as ToolStatusrev,

TableRecId as toolreq,

ToolTypeCode as Plates,

ToolCode,

StatusAvailable as StatusAvailablerev;

SQL SELECT SpecCode,

ToolStatus,

TableRecId,

ToolTypeCode,

ToolCode,

StatusAvailable

FROM PUB."PV_Tools"

WHERE ToolTypeCode LIKE 'PLATES'; 

UNQUALIFY *;

[rb]:

//Right Join (r) 

LOAD r.toollevel,

r.toollevel as SpecCode,

r.StatusAvailablerev,

r.toollevel as [Highest Rev],

    max(r.toolreq) as LastID 

Resident r

Group By r.toollevel, r.StatusAvailablerev;

drop Table r;

[tresesco]

that could be because you are aggregating it at multiple dimensions. Try like:

[rb]:

//Right Join (r)

LOAD r.toollevel,

r.toollevel as SpecCode,

r.StatusAvailablerev,

r.toollevel as [Highest Rev],

    max(r.toolreq) as LastID

Resident r

Group By r.toollevel, r.StatusAvailablerev;

[idogridish2]

may be try:

QUALIFY *;

:

LOAD SpecCode as toollevel,

ToolStatus as ToolStatusrev,

TableRecId as toolreq,

ToolTypeCode as Plates,

ToolCode,

StatusAvailable as StatusAvailablerev;

SQL SELECT SpecCode,

ToolStatus,

TableRecId,

ToolTypeCode,

ToolCode,

StatusAvailable

FROM PUB."PV_Tools"

WHERE ToolTypeCode LIKE 'PLATES';

UNQUALIFY *;

[rb]:

//Right Join (r)

LOAD r.toollevel,

r.toollevel as SpecCode,

//r.StatusAvailablerev,

r.toollevel as [Highest Rev],

    max(r.toolreq) as LastID

Resident r

Group By r.toollevelw /*, r.StatusAvailablerev*/;

drop Table r;

[bnelson111]

Works but drops the Statusavailblerev as i need to know the status of the Highest Rev

[idogridish2 · Accepted solution]

ok so you need to left join to itself:

may be try:

QUALIFY *;

:

LOAD SpecCode as toollevel,

ToolStatus as ToolStatusrev,

TableRecId as toolreq,

ToolTypeCode as Plates,

ToolCode,

StatusAvailable as StatusAvailablerev;

SQL SELECT SpecCode,

ToolStatus,

TableRecId,

ToolTypeCode,

ToolCode,

StatusAvailable

FROM PUB."PV_Tools"

WHERE ToolTypeCode LIKE 'PLATES';

UNQUALIFY *;

[rb]:

//Right Join (r)

LOAD r.toollevel,

r.toollevel as SpecCode,

//r.StatusAvailablerev,

r.toollevel as [Highest Rev],

    max(r.toolreq) as LastID

Resident r

Group By r.toollevelw /*, r.StatusAvailablerev*/;

left join

LOAD

r.toollevel,

r.toollevel as SpecCode,

r.StatusAvailablerev,

r.toollevel as [Highest Rev],

r.toolreq as LastID

Resident r;

drop Table r;

[bnelson111]

Same outcome unfortunately.

[tresesco]

You would need one more join then, like:

[rb]:

LOAD

    r.toollevel as [Highest Rev],

    max(r.toolreq) as LastID

Resident r

Group By r.toollevel;

Left Join

Load

        r.toollevel as [Highest Rev],

        r.StatusAvailablerev  

Resident r ;

Assuming toollevel is a KEY

[bnelson111]

Thats its, brilliant thanks to everyone.

[bnelson111]

Added extra join worked thanks you