Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How to use Group by?

Dear All,

This is so basic but...

How should I implement the Group by formula?

Qlik Sense Enterprise (2017 Dec?)

LOAD

    filefullpath,

    mid(filefullpath,39,2)&mid(filefullpath,42,2) as lot,

    mid(filefullpath,54,4) as iv,

    A,

    B,

    Delta_E as Delta_E_2iv,

    avg(Delta_E) as avgDelta_E,

    Delta_L as Delta_L_2iv,

    Delta_a as Delta_a_2iv,

    Delta_b as Delta_b_2iv,

    View_Id,

    Barcode

FROM [lib://xml (pjrt_p_jozsi)/Lotok\Output_0212\*.csv] 

(txt, codepage is 28591, embedded labels, delimiter is ';', msq) Group By A;

Thank you all,

Jozsef

1 Solution

Accepted Solutions
Highlighted
Champion
Champion

Hi,

the rule is:

all fields without aggregation function should be included in the Group by.

so here you will have:

.

.

.

group by

    filefullpath,

    mid(filefullpath,39,2)&mid(filefullpath,42,2),

    mid(filefullpath,54,4),

    A,

    B,

    Delta_E,,

    Delta_L ,

    Delta_a ,

    Delta_b,

    View_Id,

    Barcode;

View solution in original post

3 Replies
Highlighted
Master III
Master III

There are some rules, when you are grouping by, all the fields for which  aggregate function is not used, needs to be included in group by. So you need to check if you are grouping things correctly and what all fields need to be included in the load when group by is used.

For example as below -

ClosedGrouping data

Load fields grouped (aggregated) by ArtNo:

LOAD ArtNo, round(Sum(TransAmount),0.05) as ArtNoTotal from table.csv group by ArtNo;

Load fields grouped (aggregated) by Week and ArtNo:

LOAD Week, ArtNo, round(Avg(TransAmount),0.05) as WeekArtNoAverages from table.csv group by Week, ArtNo;

More here -

https://help.qlik.com/en-US/sense/November2017/Subsystems/Hub/Content/Scripting/ScriptRegularStateme...

Highlighted
Champion
Champion

Hi,

the rule is:

all fields without aggregation function should be included in the Group by.

so here you will have:

.

.

.

group by

    filefullpath,

    mid(filefullpath,39,2)&mid(filefullpath,42,2),

    mid(filefullpath,54,4),

    A,

    B,

    Delta_E,,

    Delta_L ,

    Delta_a ,

    Delta_b,

    View_Id,

    Barcode;

View solution in original post

Highlighted
Master III
Master III

So in your load statement, you need to have all the fields except avgDelta_E, needs to be mentioned in the Group By clause. As of now you have just mentioned 'A'.

LOAD

    filefullpath,

    mid(filefullpath,39,2)&mid(filefullpath,42,2) as lot,

    mid(filefullpath,54,4) as iv,

    A,

    B,

    Delta_E as Delta_E_2iv,

    avg(Delta_E) as avgDelta_E,

    Delta_L as Delta_L_2iv,

    Delta_a as Delta_a_2iv,

    Delta_b as Delta_b_2iv,

    View_Id,

    Barcode

FROM [lib://xml (pjrt_p_jozsi)/Lotok\Output_0212\*.csv]

(txt, codepage is 28591, embedded labels, delimiter is ';', msq) Group By A;