Solved: Re: Invalid expression - Qlik Community

Simon4 · ‎2023-04-20

Hi all,

I am getting an 'invalid expression' error because of the following querry:

What is wrong with this?

Thanks in advance.

Kind regards,

Simon

Or · ‎2023-04-20

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.

View solution in original post

Or · ‎2023-04-20

There's nothing wrong with this code, but you've only included a part of the query in question, so impossible to say what might be wrong.

Simon4 · ‎2023-04-20

Thanks for your reply.

But if I comment these lines the code runs perfectly fine so I assumed it had to do with these lines.

Or · ‎2023-04-20

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.

Simon4 · ‎2023-04-20

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;

*/

Or · ‎2023-04-20

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.

Simon4 · ‎2023-04-20

You are right! Thank you. This is how I was able to get it to run:

Invalid expression

General Question