Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I create the following table in my load script:
year | month | calendarweek |
---|---|---|
2017 | 7 | 27 |
2017 | 7 | 26 |
... | ... | ... |
Now I'd like to classify the calendarweek as first full week of the month. Week 27 for example in July 2017 would constitute a full week of that month (7 days) and week 26 wouldn't because only Saturday and Sunday are part of July.
Can you help me out?
Kind Regards
Daniel
Hey guys
Thanks for all the answers. I found a solution myself that kind of corresponds with what you suggested:
First I find out the max and min date in my data. Then I create a calendar and count the days in the weeks of the month. Consequently a flag indicates the full week.
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
/*generate year, week, month, etc. based on list of dates*/
MasterCalendar:
Load
TempDate AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
/*Count how many days are within the weeks of the respective month*/
temp_cal_1:
NoConcatenate LOAD Distinct Year,
Month,
Week,
Count(OrderDate) AS ANZ_TAGE_MONAT
Resident MasterCalendar
Group By Year,
Month,
Week
;
Kind Regards
Daniel
where do you need it, in script (in a separate table or existing table) or in frontend?!?
Hi Robin
Thanks for the reply.
I'd like to classify in the load script because I need it for further calculation. Meaning a forth column "FLAG_FULL_WEEK" which equals 1 if it is a full week of the month and 0 when not.
Regards
Daniel
Try this way:
(I used wednesday as a reference day, get start and end of the week, get it's month number and check whether it's the same
output:
Load *,
if(
((Num(Month(WeekStart(MakeWeekDate(year,week,2))))=month)
* (Num(Month(WeekEnd(MakeWeekDate(year,week,2))))=month))
, 'full week', 'partial week') AS Is_partial_week
;
Load * inline [
year, month, week
2017, 7, 27
2017, 7, 26
2017, 8, 34
2017, 8, 35
2017, 9, 35
2017, 9, 36];
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';
for i= 1 to 365
TEMP:
LOAD
Date(MakeDate(2017) + $(i) -1) as DATEFIELD AutoGenerate 1;
;
next
TABLE:
LOAD
DATEFIELD,
MonthName(DATEFIELD) as YEARMONTH,
Week(DATEFIELD) as WEEK,
WeekYear(DATEFIELD)&'-'&Week(DATEFIELD) as WEEKYEAR,
WeekDay(DATEFIELD) as DAY
Resident TEMP;
DROP Table TEMP;
Left join
LOAD
WeekYear(min(DATEFIELD))&'-'&Week(min(DATEFIELD)) as WEEKYEAR,
'X' as FULL_WEEK
Resident TABLE
Where DAY= 'Mo'
Group by YEARMONTH
;
Hi,
another solution might be:
table1:
LOAD *,
-(Month(WeekName)=Month(WeekName+6)) as FLAG_FULL_WEEK;
LOAD year,
month,
calendarweek,
WeekName(MakeWeekDate(year+(calendarweek>51 and month=1)-(calendarweek=1 and month=12),calendarweek)) as WeekName
FROM [https://community.qlik.com/thread/272492](html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 4))));
hope this helps
regards
Marco
Hey guys
Thanks for all the answers. I found a solution myself that kind of corresponds with what you suggested:
First I find out the max and min date in my data. Then I create a calendar and count the days in the weeks of the month. Consequently a flag indicates the full week.
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
/*generate year, week, month, etc. based on list of dates*/
MasterCalendar:
Load
TempDate AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
/*Count how many days are within the weeks of the respective month*/
temp_cal_1:
NoConcatenate LOAD Distinct Year,
Month,
Week,
Count(OrderDate) AS ANZ_TAGE_MONAT
Resident MasterCalendar
Group By Year,
Month,
Week
;
Kind Regards
Daniel