topic Group in Load Expression with the last entry. in QlikView

Group in Load Expression with the last entry.

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

Everyone,

What i'm attempting to do with the load script below is to only load the the latest PlannedAvailDate which is a date grouped for the ItemCode as [Last Run Item Code].

Thanks in advance.

QUALIFY ItemCode,

PlannedAvailDate,

JobCode,

JobLineNum,

JobLineStatus,

ReceivedQty;

[Job last run]:

LOAD

ItemCode as [Last Run Item Code],

PlannedAvailDate,

Month(PlannedAvailDate) as [Last Run Month Planned],

Year(PlannedAvailDate) as [Last Run Year Planned],

TEXT((JobCode) & '-' & (JobLineNum))AS [Last Run Job Number],

JobCode,

JobLineNum,

JobLineStatus,

ReceivedQty;

SQL SELECT ItemCode,

PlannedAvailDate,

JobCode,

JobLineNum,

JobLineStatus,

ReceivedQty

FROM PUB."PV_JobLine";

UNQUALIFY *;

Re: Group in Load Expression with the last entry.

its_anandrjs — Wed, 06 Dec 2017 13:44:17 GMT

What error message you get

Re: Group in Load Expression with the last entry.

bnelson111 — Wed, 06 Dec 2017 13:55:52 GMT

O sorry no error message the script above is 100%, but looking help on grouping by the item and latest date.

Re: Group in Load Expression with the last entry.

its_anandrjs — Wed, 06 Dec 2017 13:59:19 GMT

You mean to say you need to load the data for latest dates for all the item, right.

Then on the SQL table do the load this way

MaxTable:
LOAD
ItemCode as [Item Code To Load],
Date(Max(PlannedAvailDate)) as [Last Run Month Planned to Load]
Group By ItemCode;
SQL SELECT 
ItemCode,
PlannedAvailDate
FROM PUB."PV_JobLine";


QUALIFY 
ItemCode,
PlannedAvailDate,
    JobCode,
    JobLineNum,
    JobLineStatus,
    ReceivedQty;
    
[Job last run]:
LOAD
ItemCode as [Last Run Item Code],
PlannedAvailDate,
Month(PlannedAvailDate) as [Last Run Month Planned],
Year(PlannedAvailDate) as [Last Run Year Planned],
TEXT((JobCode) & '-' & (JobLineNum))AS [Last Run Job Number],
    JobCode,
    JobLineNum,
    JobLineStatus,
    ReceivedQty
    Where Exists ([Last Run Month Planned to Load],PlannedAvailDate);
SQL SELECT ItemCode,
PlannedAvailDate,
    JobCode,
    JobLineNum,
    JobLineStatus,
    ReceivedQty
FROM PUB."PV_JobLine";
UNQUALIFY *;


DROP Table MaxTable;

Re: Group in Load Expression with the last entry.

bnelson111 — Wed, 06 Dec 2017 14:06:20 GMT

Should i drop the first table? As there is still older dates being displayed for the item

Re: Group in Load Expression with the last entry.

its_anandrjs — Wed, 06 Dec 2017 14:12:50 GMT

Yes you can or may be delete that table.

Re: Group in Load Expression with the last entry.

Ralf_Heukäufer — Wed, 06 Dec 2017 14:13:07 GMT

Make a resident load and connect it over the ItemCode to the rest

Load

ItemCode,

max(PlannedAvailDate) as MaxPlannedAvailDate

resident [Job last run]

group by ItemCode;