It looks like you're loading multiple fields from FINAL_PO_FORMAT, but only grouping by a single field, which means all of the other fields need to be aggregated. Generally speaking I'd suggest not using Load * in combination with a Group By clause - spell out your fields so you can clearly see what you're working with.
If you comment out the Resident and the code runs fine, then perhaps you've included two FROM / Resident lines and one of them shouldn't be there. As I said, hard to guess without seeing the code.
This is all the code; It is a bit of a mess atm... But the important part are the last lines where the error occurs. When a partial delivery occurs I only want to show the first POSTEDDELIVERYDATE. I tried to accomplish this by min(posteddeliverydate) with a group by on POdetailid. I also tried firstsortedvalue but it gives the same error.
///// All purchase orders \\\\\
PO_Format:
//sample 0.02 //creating a sample for Tobias
LOAD "PK_R_PURCHASEORDER" as PO_id,
CREATOR as BuyerName,
date(DATECREATED, 'YYYY-MM-DD') as PODocumentDate,
"FK_SUPPLIER" as SUPPLIERID,
PURCHASEORDERNUMBER as PONumber,
ApplyMap('Shippingtype_code', FK_INCOTERM, 'No incoterm') as Incoterms1,
//ApplyMap('Shippingtype', FK_INCOTERM, 'No incoterm') as Incoterms2, //-----> Incoterm2 = locatie of leeg
'-' as Incoterms2,
date(DELIVERYDATE, 'YYYY-MM-DD') as FIRSTCONFIRMDATE, //-----> same as last
date(DELIVERYDATE, 'YYYY-MM-DD') AS LASTCONFIRMDATE,
Applymap('Currency', FK_CURRENCY, 'EUR') as AMOUNTCurrency,
ApplyMap('Paymentterm_desc', FK_PAYMENTTERM, 'No payment term') as PaymentTermDescription,
/////formatting PaymentTermCode from 07 --> N7 \\\\\\\\
If(IsNum(Replace(LTrim(Replace(ApplyMap('Paymentterm_code', FK_PAYMENTTERM, 'No payment term'),'0',' ')),' ','0')),
'N' & Replace(LTrim(Replace(ApplyMap('Paymentterm_code', FK_PAYMENTTERM, 'No payment term'),'0',' ')),' ','0'),
if(ApplyMap('Paymentterm_code', FK_PAYMENTTERM, 'No payment term') = 'VB', ApplyMap('Paymentterm_code', FK_PAYMENTTERM, 'No payment term'),
'N' & ApplyMap('Paymentterm_code', FK_PAYMENTTERM, 'No payment term'))) as PaymentTermCode,
REFERENCESUPPLIER as OrderConfirmationNumber,
//'-' as PaymentTermCode2,
//'-' as PaymentTermDescription2,
'COP_NL_BKN_GRP' as COMPANYSITEID,
//'-' as ERPCOMMODITYID,
//'-' as ERPCOMMODITYNAME,
'-' as DIRECT_INDIRECT, // have to add this ------------------
'-' AS CONFIRMATIONENTRYDATE,
'-' AS PROJECTID,
//'-' AS CostCenterCode,
//'-' AS CostCenterDescription,
// FK_JOURNALENTRY as JE_id; // for the GLA connection
;
SQL SELECT "PK_R_PURCHASEORDER",
CREATOR,
DATECREATED,
"FK_SUPPLIER",
PURCHASEORDERNUMBER,
"FK_INCOTERM",
DELIVERYDATE,
FK_CURRENCY,
FK_PAYMENTTERM,
REFERENCESUPPLIER,
FK_JOURNALENTRY
FROM "R_PURCHASEORDER";
///// Detail table goodsreceipt \\\\\
tbl_GR_detail:
LOAD "FK_GOODSRECEIPT" as GR_id,
'I-'& "FK_PURCHASEORDERDETAILITEM" as POdetailid;
SQL SELECT "FK_GOODSRECEIPT",
"FK_PURCHASEORDERDETAILITEM"
FROM "V_R_GOODSRECEIPTDETAILITEM";
Concatenate (tbl_GR_detail)
LOAD 'M-'& "FK_PURCHASEORDERDETAILMISC" as POdetailid,
"FK_GOODSRECEIPT" as GR_id;
SQL SELECT "FK_PURCHASEORDERDETAILMISC",
"FK_GOODSRECEIPT"
FROM "V_R_GOODSRECEIPTDETAILMISC";
Concatenate (tbl_GR_detail)
LOAD 'O-'& "FK_PURCHASEORDERDETAILOUTSOURCED" as POdetailid,
"FK_GOODSRECEIPT" as GR_id;
SQL SELECT "FK_PURCHASEORDERDETAILOUTSOURCED",
"FK_GOODSRECEIPT"
FROM "V_R_GOODSRECEIPTDETAILOUTSOURCED";
left join (tbl_GR_detail)
///// Lever datum \\\\\
LOAD "PK_R_GOODSRECEIPT" as GR_id,
date(DATERECEIVED, 'YYYY-MM-DD') as POSTEDRECEIPTDATE,
FK_JOURNALENTRY as JE_id; // for the GLA connection
SQL SELECT "PK_R_GOODSRECEIPT",
DATERECEIVED,
FK_JOURNALENTRY
FROM "V_R_GOODSRECEIPT";
//////GLA \\\\\\\
LEFT JOIN (tbl_GR_detail)
LOAD "FK_JOURNALENTRY" as JE_id,
"FK_GLACCOUNTTYPE" as GLA_type_id,
FK_GENERALLEDGERACCOUNT AS GLA_id;
SQL SELECT "FK_JOURNALENTRY",
"FK_GLACCOUNTTYPE",
FK_GENERALLEDGERACCOUNT
FROM "R_JOURNALENTRYDETAIL";
/*
LEFT JOIN(tbl_GR_detail)
LOAD "PK_S_GLACCOUNTTYPE" as GLA_type_id,
CODE as GLAccountNumber,
DESCRIPTION as GLAccountDescription;
SQL SELECT "PK_S_GLACCOUNTTYPE",
CODE,
DESCRIPTION
FROM "S_GLACCOUNTTYPE";
*/
LEFT JOIN(tbl_GR_detail)
LOAD "PK_R_GENERALLEDGERACCOUNT" AS GLA_id,
ApplyMap('Costcenter_code', FK_COSTCENTER) AS CostCenterCode,
ApplyMap('Costcenter_description', FK_COSTCENTER) AS CostCenterDescription,
GENERALLEDGERACCOUNTNUMBER as GLAccountNumber,
DESCRIPTION as GLAccountDescription;
SQL SELECT "PK_R_GENERALLEDGERACCOUNT",
"FK_COSTCENTER",
GENERALLEDGERACCOUNTNUMBER,
DESCRIPTION
FROM "V_R_GENERALLEDGERACCOUNT";
///// Detail table po \\\\\
tbl_PO_detail:
LOAD "FK_PURCHASEORDER" as PO_id,
'I-' & "PK_R_PURCHASEORDERDETAILITEM" as POdetailid,;
//ApplyMap('Costcenter_code', FK_COSTCENTER) AS CostCenterCode,
//ApplyMap('Costcenter_description', FK_COSTCENTER) AS CostCenterDescription
;
//ApplyMap('linenumber', 'I-' & "PK_R_PURCHASEORDERDETAILITEM") as Linenumber;
SQL SELECT "FK_PURCHASEORDER",
"PK_R_PURCHASEORDERDETAILITEM",
FK_COSTCENTER
FROM "V_R_PURCHASEORDERDETAILITEM";
Concatenate (tbl_PO_detail)
LOAD 'M-' & "PK_R_PURCHASEORDERDETAILMISC" as POdetailid,
"FK_PURCHASEORDER" as PO_id,;
//ApplyMap('Costcenter_code', FK_COSTCENTER) AS CostCenterCode,
//ApplyMap('Costcenter_description', FK_COSTCENTER) AS CostCenterDescription;
// ApplyMap('linenumber', 'M-' & "PK_R_PURCHASEORDERDETAILMISC") as Linenumber;
SQL SELECT "PK_R_PURCHASEORDERDETAILMISC",
"FK_PURCHASEORDER",
FK_COSTCENTER
FROM "V_R_PURCHASEORDERDETAILMISC";
Concatenate (tbl_PO_detail)
LOAD 'O-' & "PK_R_PURCHASEORDERDETAILOUTSOURCED" as POdetailid,
"FK_PURCHASEORDER" as PO_id,;
//ApplyMap('Costcenter_code', FK_COSTCENTER) AS CostCenterCode,
//ApplyMap('Costcenter_description', FK_COSTCENTER) AS CostCenterDescription;
//ApplyMap('linenumber', 'O-' & "PK_R_PURCHASEORDERDETAILOUTSOURCED" ) as Linenumber;
SQL SELECT "PK_R_PURCHASEORDERDETAILOUTSOURCED",
"FK_PURCHASEORDER",
FK_COSTCENTER
FROM "V_R_PURCHASEORDERDETAILOUTSOURCED";
/*
left join (tbl_PO_detail)
LOAD
//"FK_PURCHASEORDER" as PO_id,
if(SOURCE_TABLENAME = 'R_PURCHASEORDERDETAILITEM', 'I-' & "FK_PURCHASEORDERDETAILITEM",
if(SOURCE_TABLENAME = 'R_PURCHASEORDERDETAILMISC', 'M-'& "FK_PURCHASEORDERDETAILMISC",
'O-'& "FK_PURCHASEORDERDETAILOUTSOURCED")) as POdetailid,
LINENUMBER;
// SOURCE_TABLENAME;
/*'I' & "FK_PURCHASEORDERDETAILITEM" as POdetailid,
"FK_ITEM",
'M-'& "FK_PURCHASEORDERDETAILMISC" as POdetailid ,
'O-'& "FK_PURCHASEORDERDETAILOUTSOURCED" as POdetailid;
SQL SELECT LINENUMBER,
"FK_PURCHASEORDER",
"FK_PURCHASEORDERDETAILITEM",
"FK_ITEM",
"FK_PURCHASEORDERDETAILMISC",
"FK_PURCHASEORDERDETAILOUTSOURCED",
SOURCE_TABLENAME
FROM "V_R_PURCHASEORDERALLDETAIL";
*/
////////// All purchase order lines (details) \\\\\\\\\\\\
left join (tbl_PO_detail)
LOAD LINENUMBER as POLineNumber,
//"FK_PURCHASEORDER" as PO_id,
QUANTITY as POLineItemQuantity,
//GROSSPURCHASEPRICE / QUANTITY as POLineItemUnitPrice1, //Gross unit price
NETPURCHASEPRICE / QUANTITY as POLineItemUnitPrice, //Net unit price
//GROSSPURCHASEPRICE as AMOUNT1, //Gross amount (per line) ---> only net
NETPURCHASEPRICE as AMOUNT, //Net amount (per line)
"FK_ITEM" as Item_id,
DATE(DEMANDEDDELIVERYDATE, 'YYYY-MM-DD') as REQUESTEDDATE,
///// Supplier Partnumber \\\\\\\\\
if(SOURCE_TABLENAME = 'R_PURCHASEORDERDETAILITEM',
if(ApplyMap('Supplier_item_code_item', FK_PURCHASEORDERDETAILITEM) = 'Code' , ' ' , ApplyMap('Supplier_item_code_item', FK_PURCHASEORDERDETAILITEM)
),
if(SOURCE_TABLENAME = 'R_PURCHASEORDERDETAILOUTSOURCED', ApplyMap('FK_JOBORDER', FK_PURCHASEORDERDETAILOUTSOURCED), ''))
as SUPPLIERPARTNUMBER,
//// Framework contract connection \\\\\\\
if(SOURCE_TABLENAME = 'R_PURCHASEORDERDETAILITEM', ApplyMap('Framework_contract_item', FK_PURCHASEORDERDETAILITEM),
IF(SOURCE_TABLENAME = 'R_PURCHASEORDERDETAILOUTSOURCED', ApplyMap('Framework_contract_outsourced', FK_PURCHASEORDERDETAILOUTSOURCED))) as CONTRACTITEM,
DESCRIPTION AS DESCRIPTION,
'-' as QUALITY,
if(SOURCE_TABLENAME = 'R_PURCHASEORDERDETAILITEM', 'I-' & "FK_PURCHASEORDERDETAILITEM",
if(SOURCE_TABLENAME = 'R_PURCHASEORDERDETAILMISC', 'M-'& "FK_PURCHASEORDERDETAILMISC",
'O-'& "FK_PURCHASEORDERDETAILOUTSOURCED")) as POdetailid,
;
//sum(GROSSPURCHASEPRICE) as AMOUNT1,
//sum(NETPURCHASEPRICE) as AMOUNT2;
SQL SELECT LINENUMBER,
"FK_PURCHASEORDER",
QUANTITY,
GROSSPURCHASEPRICE,
NETPURCHASEPRICE,
"FK_ITEM",
DESCRIPTION,
DEMANDEDDELIVERYDATE,
SOURCE_TABLENAME,
FK_PURCHASEORDERDETAILITEM,
FK_PURCHASEORDERDETAILOUTSOURCED,
FK_PURCHASEORDERDETAILMISC
// FK_PURCHASEFRAMEWORKCONTRACT
FROM "V_R_PURCHASEORDERALLDETAIL";
////////// Frame contracts\\\\\\\\\
LEFT JOIN (tbl_PO_detail)
LOAD "PK_U_PURCHASEFRAMEWORKCONTRACT" as CONTRACTITEM,
CODE as CONTRACTID,
///// DURATION IN MONTHS \\\\\\\
//////FROM STARTDATE \\\\\\
(num(month(ENDDATE)) + ( year(ENDDATE) * 12))
-
(num(month(STARTDATE)) + ( year(STARTDATE) * 12)) AS DURATIONINMONTHS,
/*
/////FROM TODAY\\\\\
(num(month(ENDDATE)) + ( year(ENDDATE) * 12))
-
(num(month(TODAY())) + ( year(TODAY()) * 12)) AS DURATIONINMONTHS2,// ---> not neccesairy
*/
DATE(ENDDATE, 'YYYY-MM-DD') AS CONTRACTEXPIRINGDATE;
SQL SELECT "PK_U_PURCHASEFRAMEWORKCONTRACT",
CODE,
STARTDATE,
ENDDATE
FROM "U_PURCHASEFRAMEWORKCONTRACT";
///////Item details for PO lines \\\\\\\\\\
left join (tbl_PO_detail)
LOAD "PK_R_ITEM" as Item_id,
R8OMSCHRIJVING AS MaterialDescription, // dutch descriptions from old ERP system
DESCRIPTION as MaterialDescription2,// mostly english, we have dutch descriptions for old aricles but they are becomming less relevant
CODE as PARTNUMBER,
STANDARDPURCHASEPRICE as STANDARDCOST,
date(PURCHASEPRICEDATE, 'YYYY-MM-DD') AS STANDARDCOSTDATE,
'EUR' as STANDARDCOSTCURRENCY,
ApplyMap('UOM', FK_ITEMUNIT) AS POLineItemQuantityUOM,
ApplyMap('Per', FK_ITEMUNIT) AS Per,
ApplyMap('Item_group', FK_ITEMGROUP) as ERPCOMMODITYNAME,
ApplyMap('Item_groupcode', FK_ITEMGROUP) as ERPCOMMODITYID;
SQL SELECT "PK_R_ITEM",
DESCRIPTION,
CODE,
STANDARDPURCHASEPRICE,
PURCHASEPRICEDATE,
R8OMSCHRIJVING,
FK_ITEMUNIT,
FK_ITEMGROUP
FROM "V_R_ITEM";
//////Supplier information \\\\\\\\\
tbl_supplier:
LOAD "PK_R_RELATION" as SUPPLIERID,
"FK_VISITINGADDRESS" as Addres_id,
NAME as SUPPLIERNAME,
VATNUMBER as VATIDENTIFICATIONCODE,
/////formatting PaymentTermCode from 07 --> N7 \\\\\\\\
If(IsNum(Replace(LTrim(Replace(ApplyMap('Paymentterm_code', FK_PURCHASEPAYMENTTERM, 'No payment term'),'0',' ')),' ','0')),
'N' & Replace(LTrim(Replace(ApplyMap('Paymentterm_code', FK_PURCHASEPAYMENTTERM, 'No payment term'),'0',' ')),' ','0'),
if(ApplyMap('Paymentterm_code', FK_PURCHASEPAYMENTTERM, 'No payment term') = 'VB', ApplyMap('Paymentterm_code', FK_PURCHASEPAYMENTTERM, 'No payment term'),
'N' & ApplyMap('Paymentterm_code', FK_PURCHASEPAYMENTTERM, 'No payment term'))) as PaymentTermCode2,
ApplyMap('Paymentterm_desc', FK_PURCHASEPAYMENTTERM, 'No payment term') as PaymentTermDescription2,
;
SQL SELECT "PK_R_RELATION",
"FK_VISITINGADDRESS",
NAME,
VATNUMBER,
FK_PURCHASEPAYMENTTERM
FROM "V_R_RELATION";
left join (tbl_supplier)
LOAD CITY as SUPPLIERCITY,
ApplyMap('Country_ISO_2', FK_COUNTRY) AS SUPPLIERCOUNTRY,
"PK_R_ADDRESS" as Addres_id,
ZIPCODE as SUPPLIERPOSTALCODE,
ApplyMap('Supplier_state', FK_STATE, 'Outisde USA') as SUPPLIERSTATE,
STREET & ' ' & HOUSENUMBER AS SUPPLIERSTREETADDRESS;
SQL SELECT CITY,
"FK_COUNTRY",
"PK_R_ADDRESS",
ZIPCODE,
"FK_STATE",
STREET,
HOUSENUMBER
FROM "V_R_ADDRESS";
Left Join (PO_Format) LOAD * RESIDENT tbl_supplier;
drop table tbl_supplier;
Left Join (tbl_PO_detail) LOAD * RESIDENT tbl_GR_detail;
drop table tbl_GR_detail;
Left Join (PO_Format) LOAD * RESIDENT tbl_PO_detail;
drop table tbl_PO_detail;
///// code to create open PO indicator on whole table to handle Null values. If done in goodsreceipt table no calculation on nulls is posisble because they don't exist \\\\\
FINAL_PO_FORMAT:
LOAD *,
IF(LEN(POSTEDRECEIPTDATE) = 0,0,1) AS OPENPOINDICATOR
//FirstSortedValue(POdetailid, POSTEDRECEIPTDATE) AS POSTEDRECEIPTDATE2
RESIDENT PO_Format;
DROP TABLE PO_Format;
DROP FIELDS PO_id, GR_id, JE_id, GLA_type_id, GLA_id, Item_id, Addres_id;
/*
FINAL_PO_FORMAT2:
LOAD*,
MIN(POSTEDRECEIPTDATE) AS POSTEDRECEIPTDATE2
RESIDENT FINAL_PO_FORMAT
group by POdetailid;
DROP TABLE FINAL_PO_FORMAT;
*/
It looks like you're loading multiple fields from FINAL_PO_FORMAT, but only grouping by a single field, which means all of the other fields need to be aggregated. Generally speaking I'd suggest not using Load * in combination with a Group By clause - spell out your fields so you can clearly see what you're working with.
