How to use Group by?

[jozsefpeitli]

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

[Digvijay_Singh]

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 -

Grouping 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...

[YoussefBelloum · Accepted solution]

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;

[Digvijay_Singh]

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;