Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
bnelson111
Contributor

Group in Load Expression with the last entry.

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

6 Replies

Re: Group in Load Expression with the last entry.

What error message you get

bnelson111
Contributor

Re: Group in Load Expression with the last entry.

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.

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
Contributor

Re: Group in Load Expression with the last entry.

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.

Yes you can or may be delete that table.

Partner
Partner

Re: Group in Load Expression with the last entry.

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;