Max date in data load editor

Simon4 — Thu, 26 Jan 2023 08:39:37 GMT

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

Re: Max date in data load editor

MayilVahanan — Thu, 26 Jan 2023 08:46:52 GMT

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;

topic Re: Max date in data load editor in App Development

Max date in data load editor

Re: Max date in data load editor