topic Re: Group by statement in QlikView

Group by statement

jleefjcapital — Thu, 27 Oct 2016 22:17:08 GMT

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:

Re: Group by statement

sunny_talwar — Thu, 27 Oct 2016 22:19:04 GMT

What is the script you have used?

Re: Group by statement

jleefjcapital — Thu, 27 Oct 2016 22:21:56 GMT

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 ;

Re: Group by statement

sunny_talwar — Thu, 27 Oct 2016 22:25:06 GMT

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 ;

Re: Group by statement

maksim_senin — Thu, 27 Oct 2016 22:30:39 GMT

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

Re: Group by statement

jleefjcapital — Fri, 28 Oct 2016 14:04:23 GMT

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.

Re: Group by statement

jleefjcapital — Fri, 28 Oct 2016 14:05:22 GMT

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.

Re: Group by statement

sunny_talwar — Fri, 28 Oct 2016 14:06:38 GMT

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

DROP Table Total_Nonfarm_SA_thous;

Re: Group by statement

jleefjcapital — Fri, 28 Oct 2016 14:13:50 GMT

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

Re: Group by statement

jleefjcapital — Fri, 28 Oct 2016 20:14:33 GMT

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;

Re: Group by statement

sunny_talwar — Fri, 28 Oct 2016 20:17:34 GMT

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;

Re: Group by statement

jleefjcapital — Fri, 28 Oct 2016 20:29:50 GMT

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

Re: Group by statement

sunny_talwar — Fri, 28 Oct 2016 20:31:23 GMT

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