Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I have date field , I want to group all the date field on month basis. I am using the below code but still its not working.
LOAD
date(Month,'MMM YYYY') as Month,
Collection
FROM
[..\Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);
Final:
Load Month,
Sum(Collection)as Premium
Resident Data
Group by Month;
Attached is the sample files
Updated sample qvw file attached
Data:
LOAD Date(MonthStart(Month),'MMM YYYY') as Month,
Collection
FROM [Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);
Final:
LOAD Month,
Sum(Collection)as Premium
Resident Data
Group by Month;
DROP Table Data;
Try
Directory;
Data:
LOAD Month(Month) as Month,
Year(Month) as Year,
Collection
FROM
(ooxml, embedded labels, table is Sheet1);
Final:
Load Month&'-'&Year as Month_Year,
Sum(Collection)as Premium
Resident Data
Group by Year,Month;
drop table Data;
Try adding MonthStart() function to your Month field
LOAD
date(MonthStart(Month),'MMM YYYY') as Month,
Collection
FROM
[..\Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);
Updated sample qvw file attached
Data:
LOAD Date(MonthStart(Month),'MMM YYYY') as Month,
Collection
FROM [Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);
Final:
LOAD Month,
Sum(Collection)as Premium
Resident Data
Group by Month;
DROP Table Data;
Hi,
Attached is the sample file. I have unique policy id which is having one to one relation with date. I want to aggregate the date value with month irrespective of Policy ids.
You dont need that policy ID at all?? then the above script works for you.
Directory;
data:
LOAD Policy,
Monthstart(Date) as Month,
Collection_Amount
FROM
[Sample 1.xlsx]
(ooxml, embedded labels, table is recaudacion_042016);
Load Month(Month),sum(Collection_Amount)
Resident data
group by Month;
May be like this (Make sure you use MonthStart and not MonthsStart)
Data:
LOAD Policy,
Date(MonthStart([Date]), 'MMM-YYYY') as Month,
Collection_Amount
FROM
[Sample 1.xlsx]
(ooxml, embedded labels, table is recaudacion_042016);
Final:
LOAD Month,
Sum(Collection_Amount)as Premium
Resident Data
Group by Month;
DROP Table Data;
Thank you so much!!! it was typo..