topic Re: Dropping Tables in Load Statement. Find highest Value. in QlikView

Dropping Tables in Load Statement. Find highest Value.

bnelson111 — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Dropping Tables in Load Statement. Find highest Value.

tresB — Tue, 07 Jun 2016 07:46:49 GMT

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;

Re: Dropping Tables in Load Statement. Find highest Value.

idogridish2 — Tue, 07 Jun 2016 07:49:25 GMT

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;

Re: Dropping Tables in Load Statement. Find highest Value.

bnelson111 — Tue, 07 Jun 2016 11:02:44 GMT

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

Re: Dropping Tables in Load Statement. Find highest Value.

idogridish2 — Tue, 07 Jun 2016 11:09:19 GMT

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;

Re: Dropping Tables in Load Statement. Find highest Value.

bnelson111 — Tue, 07 Jun 2016 11:11:05 GMT

Same outcome unfortunately.

Re: Dropping Tables in Load Statement. Find highest Value.

tresB — Tue, 07 Jun 2016 11:11:23 GMT

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

Re: Dropping Tables in Load Statement. Find highest Value.

bnelson111 — Tue, 07 Jun 2016 16:49:01 GMT

Thats its, brilliant thanks to everyone.

Re: Dropping Tables in Load Statement. Find highest Value.

bnelson111 — Tue, 07 Jun 2016 16:49:47 GMT

Added extra join worked thanks you