Group data in to Date Ranges based on a date column

[andymanu]

Hi All,

I want to group participant's attendance related data in to date ranges.

Assume my original data set as below,

Name	Date
Mark	10/07/2018
David	18/08/2018
Alan	19/08/2018
Simon	21/08/2018
Peter	13/07/2018
Alan	13/08/2018
Marcus	12/07/2018
Brenden	22/08/2018
Nick	15/08/2018
Tim	16/07/2018
Brian	20/08/2018
Wisla	3/09/2018
Tom	26/08/2018
Andrew	5/09/2018
Thomas	10/09/2018

I want to base my grouping of my participants based on the below group list,

Date Ranges

04/07/2018 - 18/07/2018

19/07/2018 - 01/08/2018

02/08/2018 - 16/08/2018

17/08/2018 - 31/08/2018

01/09/2018 - 15/09/2018

The final outcome of the problem should be something like below,

Date Range	Count of Participants
04/07/2018 - 18/07/2018	4
19/07/2018 - 01/08/2018	0
02/08/2018 - 16/08/2018	2
17/08/2018 - 31/08/2018	6
01/09/2018 - 15/09/2018	3

My requirement is to add a new dimension during the data load process to count the grouped participants count.

Since the date range will  gradually increase over the time and above I have created a data set only for a selected months. However, the data set will have a minimum date and a maximum date at any given time period. Thus, I am planning to use ApplyMap() function to map the date field against the date range. 

I have also attached the data sheet for your easy reference.

Appreciate if someone could help me with this regards.

Thanks in advance.

Kind regards,

Andy

[PradeepReddy · Accepted solution]

try like this...

DateRange:
LOAD [Date Ranges],
Date#(trim(subfield([Date Ranges],'-',1)),'DD/MM/YYYY') as MinDate,
Date#(trim(subfield([Date Ranges],'-',2)),'DD/MM/YYYY') as MaxDate
FROM [Grouping Data.xlsx] (ooxml, embedded labels, table is [Date Ranges]);


Attendance:
LOAD Name, Date, Date(trim(date#(Date,'MM/DD/YYYY')),'DD/MM/YYYY') as DateKey
FROM [Grouping Data.xlsx]
(ooxml, embedded labels, table is [Attendance Data]);

Left Join(DateRange)
Interval_Table:
Intervalmatch(DateKey)
Load MinDate,MaxDate
Resident DateRange;

[andymanu]

Hi Pradeept,

Thanks for your valuable prompt reply.

However, when I run the script, the outcome looks something like below,

Date Ranges	=Count(Name)
01/09/2018 - 15/09/2018	1
02/08/2018 - 16/08/2018	1
04/07/2018 - 18/07/2018	2
17/08/2018 - 31/08/2018	2
19/07/2018 - 01/08/2018	0
-	2

Please find the attached screenshot I got for the above table.

It is slightly different from the expected outcome and appreciate your feedback on this.

Thank you very much.

Kind regards,

Andy

[andymanu]

Hi Pradeept,
Extremely sorry. My mistake.
I was referring to a wrong data source.
Thank you very much and it's working perfectly.
appreciate your help.
Kind regards,
Andy