Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;