Group in Load Expression with the last entry.

[bnelson111]

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 *;

[its_anandrjs]

What error message you get

[bnelson111]

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

[its_anandrjs]

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;

[bnelson111]

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

[its_anandrjs]

Yes you can or may be delete that table.

[Ralf_Heukäufer]

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;