Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a qvd file which has traffic for daily level basically it has the traffic for a single day for each hour for each location.
Now, i have to use this QVD and create another one such as this one should give me the total traffic for the week for all stores.( the whole traffic should be set to the first day of the week i.e. the first day of every week will give me the total for that week)
Attached is my QVW which i am working on, I am joining this one to Date QVD which may help us to aggregate it to week level and the date one has all the date fields required.
Any suggestions or ideas?
Thanks
load
date(weekstart(date#(DateId,'YYYYMMDD')),'YYYYMMDD') AS aggrWeekStart, // as a date into YYYYMMDD format
LocationNum as aggrLocationNum,
sum(NumVisits) as aggrNumVisits
Resident Traffic
Group by weekstart(date#(DateId,'YYYYMMDD')), LocationNum;
load
NUM#(TEXT(date(weekstart(date#(DateId,'YYYYMMDD')),'YYYYMMDD'))) AS aggrWeekStart, // as a NUMBER
LocationNum as aggrLocationNum,
sum(NumVisits) as aggrNumVisits
Resident Traffic
Group by weekstart(date#(DateId,'YYYYMMDD')), LocationNum;
Hi.
Use group by:
LOAD
LocationId,
WeekName(DateId) as DateId,
Sum(NumVisits) as TotalNumVisits
Resdient Traffic
Group by LocationId, WeekName(DateId);
Is there any reason that you load DateId so strangely? So that the text representation shows 20120129 but the numeric date corresponds to 1/14/56987.
load
weekstart(date#(DateId,'YYYYMMDD')) AS aggrWeekStart,
LocationNum as aggrLocationNum,
sum(NumVisits) as aggrNumVisits
Resident Traffic
Group by weekstart(date#(DateId,'YYYYMMDD')), LocationNum;
Or this if you don´t need it by location
load
weekstart(date#(DateId,'YYYYMMDD')) AS aggrWeekStart,
sum(NumVisits) as aggrNumVisits
Resident Traffic
Group by weekstart(date#(DateId,'YYYYMMDD'));
load
date(weekstart(date#(DateId,'YYYYMMDD')),'YYYYMMDD') AS aggrWeekStart, // as a date into YYYYMMDD format
LocationNum as aggrLocationNum,
sum(NumVisits) as aggrNumVisits
Resident Traffic
Group by weekstart(date#(DateId,'YYYYMMDD')), LocationNum;
load
NUM#(TEXT(date(weekstart(date#(DateId,'YYYYMMDD')),'YYYYMMDD'))) AS aggrWeekStart, // as a NUMBER
LocationNum as aggrLocationNum,
sum(NumVisits) as aggrNumVisits
Resident Traffic
Group by weekstart(date#(DateId,'YYYYMMDD')), LocationNum;