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: 
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
Author

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
Not applicable
Author

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

MK_QSL
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;

Not applicable
Author

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

Anonymous
Not applicable
Author

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
Author

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

Not applicable
Author

thanks, this works very well

MK_QSL
MVP
MVP

No it will not... 🙂

Anonymous
Not applicable
Author

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