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]

What is the script you have used?

[jleefjcapital]

Here is the entire script -

Total_Nonfarm_SA_thous:
LOAD
    series_id,
    "year",
    period,
    Date(Makedate("year",right(period,2)), 'MMMYYYY') as MONTHYEAR,
    value,
    If(series_id = 'SMS01000000000000001', value) as [Alabama_NF_thous],  
    If(series_id = 'SMS02000000000000001', value) as [Alaska_NF_thous]
   
FROM [lib://BLS Employment Series AllData]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Load
MONTHYEAR,
[Alabama_NF_thous],
[Alaska_NF_thous]
Resident Total_Nonfarm_SA_thous
Group by MONTHYEAR ;

[sunny_talwar]

May be this:

Total_Nonfarm_SA_thous:
LOAD
    series_id,
    "year",
    period,
    Date(Makedate("year",right(period,2)), 'MMMYYYY') as MONTHYEAR,
    value,
    If(series_id = 'SMS01000000000000001', value) as [Alabama_NF_thous], 
    If(series_id = 'SMS02000000000000001', value) as [Alaska_NF_thous]
   
FROM [lib://BLS Employment Series AllData]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Load
MONTHYEAR,
Sum([Alabama_NF_thous]) as [Alabama_NF_thous],
Sum([Alaska_NF_thous]) as [Alaska_NF_thous]
Resident Total_Nonfarm_SA_thous
Group by MONTHYEAR ;

[maksim_senin]

Exactly!

When you use Group By you should use some aggr. function in the Load. Please give a look at the manual.

Best regards,

Maxim

[jleefjcapital]

Thanks, Sunny.  It loaded, but it still hasn't resolved the original problem.  When I place them in the table, there are still lots of duplicate dates and nulls.... I'm wondering if there's a another solution for this.

[jleefjcapital]

Thanks, Max.

I was actually looking for the manual.  I'm very new to Qlik, and it would be great if you could point me to the manual for Qlik script. 

[sunny_talwar · Accepted solution]

I think you might have to DROP Total_Nonfarm_SA_thous at the end by adding this statement:

DROP Table Total_Nonfarm_SA_thous;

[jleefjcapital]

Thanks, Sunny!!!!!  You're awesome!!!!!    It worked like a charm. 

[jleefjcapital]

Sunny,

I tried rerunning the group by statement, but it's not working.  I'm not sure what I'm doing wrong this time.

Here's the original script.

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 ;
Drop Table BLS_data_1;