Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Not applicable

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;

View solution in original post

10 Replies
Highlighted
Not applicable

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

Highlighted
MVP
MVP

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;

Highlighted
Not applicable

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;

View solution in original post

Highlighted
Creator
Creator

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

Highlighted
Creator II
Creator II

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

Highlighted
Not applicable

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

Highlighted
Not applicable

thanks, this works very well

Highlighted
MVP
MVP

No it will not... 🙂

Highlighted
Creator II
Creator II

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