Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jleefjcapital
Creator II
Creator II

Group by statement

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: 

1 Solution

Accepted Solutions
sunny_talwar

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

DROP Table Total_Nonfarm_SA_thous;

View solution in original post

12 Replies
sunny_talwar

What is the script you have used?

jleefjcapital
Creator II
Creator II
Author

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
Partner - Creator III
Partner - Creator III

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

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
Creator II
Creator II
Author

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

jleefjcapital
Creator II
Creator II
Author

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;