Group by statement

[jleefjcapital]

Hi Everyone,

I'm trying to resolve an issue with my data loads where the fields are not being joined by date.  As a result, when I table them together I end up with a lot of nulls in adjacent cells and duplicate dates.  Please see screenshot #1 .   So I tried using the Group By statement to join the fields by MonthYear, which is the preferred date format.   However, I get an error message , pls see Screenshot #2.

I hope someone would be able to help me.  Thanks in advance.

I used the following script after my main load step.

Load *

Resident Total_Nonfarm_SA_thous

Group by MONTHYEAR ;

Screenshot #1:

Screenshot #2: 

[sunny_talwar]

Anytime you have a non-aggregating field (one which doesn't have Sum, Min, Max, Median, Only, Concat.... function), you need to add it to the Group By Statement

BLS_data_1:
LOAD
    series_id,
    "year",
    period,
    value,
    Date(Makedate("year",right(period,2)),'M/D/YYYY') as DATE,
    Date(Makedate("year",right(period,2)), 'MMMYYYY') as MONTHYEAR,
    If(series_id ='SMS01000000000000001', value) as [Alabama],
    If(series_id ='SMS02000000000000001', value) as [Alaska]

FROM [lib://BLS Employment Series AllData]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

BLS_data:
LOAD
    MONTHYEAR,
    DATE,
    SUM([Alabama]) as [Alabama_NF],
    SUM([Alaska]) as [Alaska_NF]

Resident BLS_data_1
Group by MONTHYEAR, DATE ;
Drop Table BLS_data_1;

[jleefjcapital]

Thank you!  Also, do you know where I can obtain the manual referenced by Maxim?

[sunny_talwar]

I am not sure which manual is he referring to, but going throught QlikView and Qlik Sense help can be helpful

Home ‒ QlikView

Home ‒ Qlik Sense