invalid expression, group by related or caused by preceding load?

[Anonymous]

Hi Guys,

I keep getting an error message: invalid expression. I wonder if that's being caused by a preceding load or because i'm missing a field in the group by.

I can do a resident load, however want to do that unless that is the only way.

Below is my script:

SYSTEM1:

LOAD

*,

Date(MakeDate(Year,Month,1),'YYYYMM')    AS Period

Sum(tSales)   AS Sales

;

Load

    Shipments.Area,

    Shipments.DepCountry,

    Shipments.DisCountry,

    Shipments.ShipmentRef                         AS ShipmentReference,

    VG,

    KeyMultiplier,

    KeyAccount,

    System.income,

    Shipments.shpmdate,

    if(match(KeyMultiplier, 'CII', 'OCO', 'OCI', 'CIO'),

    System.income*-1, System.income) AS tSales,

    IF(match(Shipments.Area='AI','SI'), Shipments.DepCountry, Shipments.DisCountry) AS Country,

    Year(Shipments.shpmdate)                    as Year,

    Month(Shipments.shpmdate)                  as Month,

    Shipments.TEU                                     as TEU,

    Max(Shipments.shpmcwgt)                    as TaxWeight,

    Max(Shipments.GrossWeight)                as GrossWeight,

    CBM

FROM

(qvd)

Group by

    Shipments.Area,

    PROCARSCHARGES.ChargeAmountHome,

    Shipments.DepCountry,

    Shipments.DisCountry,

    Shipments.ShipmentRef,

    VG,

    Shipments.TEU,

    Shipments.shpmcwgt,

    Shipments.GrossWeight,    

    KeyAccount,

    Shipments.shpmdate,

    ;

Drop fields  tSales;

Hope some one can help!

Cheers!

Sam

[swuehl]

Sam,

what are you trying to achieve here?

You are using a long list of group by fields here, including fields that you do aggregate (but here I don't see a need to group by them), but excluding fields that are used in your load not as argument to an aggregation function.

You will need to aggregate all fields from your input table that are not listed in the group by clause, but used in the load (e.g.KeyMultiplier, shpmdate).

Hope this helps,

Stefan

[Anonymous]

Hi Stefan,

Sorry for the late response and vage description. Let me clearfily what I would like to achieve .

We have a database with all the shipmentinfo. Each shipmentnr# is unique and therefore used as a key.

However the problem arises when I want the turnover of each shipmentnr#. This is being caused due to verious charges that we charge the customer. for example: we charge the customer for the shipment, administration, taxes, packiging etc. Sometimes there are more than 20 chargescodes. All those charges together gives you the turnover of 1 shipment.

Because I want to load data from 2009 till 2012 (last three years), I would proberbly get more than 7 milion records. Therefore I wanted to group by all the chargecodes together to get 1 turn over per shipment.

The other reason is, that each shipment is no longer unique due to all charges codes. If I would count the shipmentnr's, I would get an invalid output, unless I use the Distinct qualifier.

The other problem that occurs is that each shipment has 1 grossweight, for example 20 KG per shipmentnr#. So the right output should be 20 KG, because 1 shipment has 7, 8,9 or sometimes 20 chargescodes, the grossweight is multiplied by the chargescodes. so 20 KG turns to 140 KG, if a chargecode has 7 rows/lines.

I therefore wanted to solve it by aggreating all the chargecodes, in order to create one row per shipment nr, and one row for grossweight and a unqiue shipmentKey.

Hopes that clearify's my goal

I've removed and added some fields, the group by now works.  If you know a better approch to solve the above then I'm all ears

Like always, Thanks for your help!!! I really appriciate it

Cheers

Sam