Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
chesterluck
Contributor II

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

Re: Group by lead to invalid expression

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

7 Replies
MVP
MVP

Re: Group by lead to invalid expression

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.

chesterluck
Contributor II

Re: Group by lead to invalid expression

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

chesterluck
Contributor II

Re: Group by lead to invalid expression

I just tried to solve the problem through

Group by lJobRefForAggr

But it leads to the same error "Invalid expression"

//chesterluck

MVP
MVP

Re: Group by lead to invalid expression

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;

chesterluck
Contributor II

Re: Group by lead to invalid expression

  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?

MVP
MVP

Re: Group by lead to invalid expression

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

chesterluck
Contributor II

Re: Group by lead to invalid expression

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

Community Browser