Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bnelson111
Creator II
Creator II

Dropping Tables in Load Statement. Find highest Value.

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.

table1.jpg

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;

1 Solution

Accepted Solutions
idogridish2
Creator III
Creator III

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;

View solution in original post

8 Replies
tresesco
MVP
MVP

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
Creator III
Creator III

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
Creator II
Creator II
Author

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

idogridish2
Creator III
Creator III

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
Creator II
Creator II
Author

Same outcome unfortunately.

tresesco
MVP
MVP

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
Creator II
Creator II
Author

Thats its, brilliant thanks to everyone.

bnelson111
Creator II
Creator II
Author

Added extra join worked thanks you