topic error when try to use count in proceeding load in QlikView

error when try to use count in proceeding load

Fri, 31 Oct 2014 14:51:50 GMT

Hi,

I try to use count distinct function calculate how many weeks in one month, I add this in a proceeding load script as below,

mapQuarterName:

Mapping LOAD * INLINE [

mapQuarter, mapQuartername

1, jan-mar

2, apr-jun

3, jul-sep

4, oct-dec

];

Let varMinDate = 41275;//calendar start from 2013-01-01

Let varMaxDate = 43465;//calendar end to 2018-12-31

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

DATE:

LOAD//Proceeding Load create NoOfWeek

count(distinct(yearweek)) AS NoOfWeek

Group By yearmonth;

LOAD

TempDate AS Date,

Year(TempDate) As year,

Ceil(Month(TempDate)/3) AS quarter,

ApplyMap('mapQuarterName',Ceil(Month(TempDate)/3)) &' '& Year(TempDate) AS quartername ,

date(monthstart(TempDate), 'YYYYMM') AS yearmonth,

WeekYear(TempDate)*100+Week(weekstart(TempDate)) as yearweek

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

When I run it, I got error message, can I use a count function in proceeding load? how?

Thanks a lot!

alex

Re: error when try to use count in proceeding load

Fri, 31 Oct 2014 14:53:37 GMT

Try not to use the group by in the preceding load. That is what generates the error.

Re: error when try to use count in proceeding load

MK_QSL — Fri, 31 Oct 2014 14:58:30 GMT

Try

DATE:

LOAD//Proceeding Load create NoOfWeek

count(distinct(yearweek)) AS NoOfWeek

Group By Date,year, quarter,quartername, yearmonth;

LOAD

TempDate AS Date,

Year(TempDate) As year,

Ceil(Month(TempDate)/3) AS quarter,

ApplyMap('mapQuarterName',Ceil(Month(TempDate)/3)) &' '& Year(TempDate) AS quartername ,

date(monthstart(TempDate), 'YYYYMM') AS yearmonth,

WeekYear(TempDate)*100+Week(weekstart(TempDate)) as yearweek

Resident TempCalendar

Order By TempDate ASC;

Re: error when try to use count in proceeding load

Fri, 31 Oct 2014 14:58:56 GMT

Hi Alex,

You may want to join the new field like this:

//remove the preceding load and place the code below after loading the DATE table

Left Join(DATE)

LOAD

yearmonth,

count(distinct(yearweek)) AS NoOfWeek

Resident DATE

Group By yearmonth;

Re: error when try to use count in proceeding load

shambhub — Fri, 31 Oct 2014 15:02:39 GMT

Alex,

When you perform aggregate function in select statement you should end with Group By statement, In Group By syntax you should include all the fields which are included in Select statement.

Exa:

Load A, B, Count(C) as C1

from Tab1

Group By A, B

Re: error when try to use count in proceeding load

Anonymous — Fri, 31 Oct 2014 15:07:07 GMT

Wow, Manish:

Does that syntax work?! If so, that's going in the toolbox, for sure.

Do you know how this performance would compare against a join on an additional resident load (as I would normally do)?

Re: error when try to use count in proceeding load

Fri, 31 Oct 2014 15:07:58 GMT

I got the same error, so I think as kavrosDA suggested I should avoid group by in proceed load

Re: error when try to use count in proceeding load

Fri, 31 Oct 2014 15:09:04 GMT

thanks, this works very well

Re: error when try to use count in proceeding load

MK_QSL — Fri, 31 Oct 2014 15:10:21 GMT

No it will not... 🙂

Re: error when try to use count in proceeding load

Anonymous — Fri, 31 Oct 2014 15:13:25 GMT

Thought it looked too good to be true. That sure would clean up some of my code, though.

Re: error when try to use count in proceeding load

Fri, 31 Oct 2014 15:13:34 GMT

nice try though