Group by lead to invalid expression

[Anonymous]

Hello,

I have a problem with one load statement:

I've figured already out that "group by lJobRef " is causing the problem... Please help

Job_NEW_Plus_Min:
LOAD lJobRef,
     dSetQuant,
     dSetShots,
     dSetSpeed,
     iActCavity,
     iSetCavity,
     Auftragsnummer,
     sType,
     tActBegin,
     tActEnd,
     tReleaseDate,
     Min(lTolRefArt) as lTolRefArt,
     Artikelbeschreibung,
     Artikel,
     Werkzeuglagerort,
     Werkzeugbeschreibung,
     Werkzeug,
     Maschinenplatz,
     Maschinenbeschreibung,
     Maschine,
     0 as bZusatz

FROM
..\..\02_Data\JOB_incl_ART_MASCHINE_WRZ.qvd
(qvd)

Where substringcount(Auftragsnummer,'+')>0
group by lJobRef;
Job_NEW_Plus_NICHT:
LOAD lJobRef,
     dSetQuant,
     dSetShots,
     dSetSpeed,
     iActCavity,
     iSetCavity,
     Auftragsnummer,
     sType,
     tActBegin,
     tActEnd,
     tReleaseDate,
     lTolRefArt,
     Artikelbeschreibung,
     Artikel,
     Werkzeuglagerort,
     Werkzeugbeschreibung,
     Werkzeug,
     Maschinenplatz,
     Maschinenbeschreibung,
     Maschine,
     0 as bZusatz

FROM
..\..\02_Data\JOB_incl_ART_MASCHINE_WRZ.qvd
(qvd)

Where substringcount(Auftragsnummer,'+')=0 Group BY lJobRef;

[swuehl]

From the HELP:

group by is a clause used for defining over which fields the data should be aggregated (grouped). The aggregation fields should be included in some way in the expressions loaded. No other fields than the aggregation fields may be used outside aggregation functions in the loaded expressions.

So either include all your loaded fields in the group by clause (NO, I wouldn't do this), or use aggregation functions with the ones not included.

[Anonymous]

So you mean that I can't use

Load lJobRef

...

...

Group By lJobRef

Is that right?

And if I still want to do it , I should before that load statement do sth like this

Load lJobRef as lJobRefForAggr

lJodRef

And Later

Load lJobRef

..

..

Group by lJobRefForAggr

Please correct if I'm wrong

Thank You in advance

//chesterluck

[Anonymous]

I just tried to solve the problem through

Group by lJobRefForAggr

But it leads to the same error "Invalid expression"

//chesterluck

[swuehl]

No, that's not what I intended to say.

You can't do something like

LOAD A, B

resident TABLE group by A;

You need to use aggregation functions with all fields that you want to load, but are not listed in the group by clause (i.e. B in this example).

It's more or less the same as creating a table chart with dimension A, just inputting B as expression will not result in a something meaningful in most cases (unless the implicitely used only() function returns a value). Using group by in the script is just a little more strict, you need to explicitely use an aggregation function with B:

LOAD

     A,

      sum(B)          // or some other script aggregation function (only(), min(), max(), avg() ....)

resident TABLE group by A;

[Anonymous]

  So you mean for this expression :

LOAD

lJobRef,
dSetQuant,
dSetShots,
dSetSpeed,
iActCavity,
iSetCavity,
Auftragsnummer,
sType,
tActBegin,
tActEnd,
tReleaseDate,
MAx(lTolRefArt) as lTolRefArt,
Artikelbeschreibung,
Artikel,
Werkzeuglagerort,
Werkzeugbeschreibung,
Werkzeug,
Maschinenplatz,
Maschinenbeschreibung,
Maschine,
1 as bZusatz
FROM
..\..\02_Data\JOB_incl_ART_MASCHINE_WRZ.qvd
(qvd)
Where substringcount(Auftragsnummer,'+')>0

//I need a Group by:

Group BYEVERYTHING except lTolRefArt as it is the aggr. field ;

Right?

[swuehl · Accepted solution]

Group by every Input field that is used in the load, right (so you don't need to group by bZusatz).

But I think you don't really want to group by all these fields. In most cases, you only have some few fields you want to group by, that describe the relevant context you need for your aggregations.

For example, you don't need any fields that are just detailing descriptions of some others (like maybe Maschine -> Maschinenbeschreibung). Then you don't want to group by any field that will restrict your context to portions too small (maybe when using timestamps).

Just use the minimum required number of group by fields, then join your resulting table back on another table (or keep the tables separate but linked).

Well, it's up to you how to design your data model.

Regards,

Stefan

[Anonymous]

And its working.

As proposed I used the Join statement.

The main problemwas that I tried to add

1 as bZusatz

This I just did in the second step.

And at the end I left joined the orig. table

Thanx a lot

//chesterluck