Max date in data load editor

[Simon4]

Hi all,

i have these tables:

tbl_main_project:
LOAD PROJECTNUMBER as Project_number,
    PROJECTNAME as Project_name,
    STARTDATE as Startdate_mp,
    ENDDATE as Enddate_mp,
    "PK_R_MAINPROJECT" as Mainproject_id,
    "FK_PROJECTCALCULATION" as Project_calculation_id,
    "FK_RELATION" as Relation_id,
    FINANCIALDONE;
SQL SELECT PROJECTNUMBER,
    PROJECTNAME,
    "FK_PROJECTCATEGORY",
    STARTDATE,
    ENDDATE,
    "PK_R_MAINPROJECT",
    "FK_PROJECTCALCULATION",
    "FK_RELATION",
    FINANCIALDONE
FROM "Bakon Food Equipment B.V.".dbo."V_R_MAINPROJECT";

LOAD ORDERNUMBER,
    "PK_R_ORDER" as Order_id,
    "FK_MAINPROJECT" as Mainproject_id,
    DATECONFIRMED,
    TOTALNETAMOUNT,
    FINANCIALDONE as FINANCIALDONE_ORDER,
    DESCRIPTION,
    FK_RELATION as Relation_id_CCM,
    FK_ORDERCATEGORY as Order_cat_id,
    DELIVERYDATE,
    REALTOTALMATERIAL,
    REALTOTALMISC,
    REALTOTALOUTSOURCED,
    REALTOTALWORK,
    DATEFINANCIALFINISHED,
    year(DATEFINANCIALFINISHED) as YEARFINANCIALFINISHED,
    num(month(DATEFINANCIALFINISHED)) as MONTHFINANCIALFINISHED;
SQL SELECT ORDERNUMBER,
    "PK_R_ORDER",
    "FK_MAINPROJECT",
    DATECONFIRMED,
    TOTALNETAMOUNT,
    FINANCIALDONE,
    DESCRIPTION,
    FK_RELATION,
    FK_ORDERCATEGORY,
    DELIVERYDATE,
    REALTOTALMATERIAL,
    REALTOTALMISC,
    REALTOTALOUTSOURCED,
    REALTOTALWORK,
    DATEFINANCIALFINISHED
FROM "Bakon Food Equipment B.V.".dbo."V_R_ORDER";

Every main project has multiple orders.  I need the max(DATEFINANCIALFINISHED) for every project. 

I am going to use this as a dimension so the calculation has to be done in the data load editor. unfortunatly i am unable to get it right. 

Can some one help me?

kind regards,

Simon

[MayilVahanan · Accepted solution]

HI

As Mainproject_id already there in Order table, then you can try like below

MaxDateperProject:

Load Mainproject_id, Max(DATEFINANCIALFINISHED) as MaxDate resident V_R_ORDER

group by Mainproject_id;