Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Try not to use the group by in the preceding load. That is what generates the error.
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;
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;
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
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)?
I got the same error, so I think as kavrosDA suggested I should avoid group by in proceed load
thanks, this works very well
No it will not... 🙂
Thought it looked too good to be true. That sure would clean up some of my code, though.