Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Group by lead to invalid expression

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;

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

7 Replies
swuehl
MVP
MVP

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
Not applicable
Author

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
Not applicable
Author

I just tried to solve the problem through

Group by lJobRefForAggr

But it leads to the same error "Invalid expression"

//chesterluck

swuehl
MVP
MVP

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
Not applicable
Author

  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
MVP
MVP

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
Not applicable
Author

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