Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

error when try to use count in proceeding load

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?

2014-10-31 15-48-47.jpg

Thanks a lot!

alex

1 Solution

Accepted Solutions
Not applicable

Re: error when try to use count in proceeding load

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;

10 Replies
Not applicable

Re: error when try to use count in proceeding load

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

MVP
MVP

Re: error when try to use count in proceeding load

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;

Not applicable

Re: error when try to use count in proceeding load

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;

shambhub
Contributor

Re: error when try to use count in proceeding load

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

stevenrtaylor
Contributor II

Re: error when try to use count in proceeding load

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)?

Not applicable

Re: error when try to use count in proceeding load

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

Not applicable

Re: error when try to use count in proceeding load

thanks, this works very well

MVP
MVP

Re: error when try to use count in proceeding load

No it will not... :-)

stevenrtaylor
Contributor II

Re: error when try to use count in proceeding load

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

Community Browser