Qlik Community

Qlik Support Discussions

Discussion Board for collaboration related to Qlik Support.

Highlighted
andymanu
Contributor

Group data in to Date Ranges based on a date column

Hi All,

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

Assume my original data set as below,

NameDate
Mark 10/07/2018
David18/08/2018
Alan19/08/2018
Simon21/08/2018
Peter13/07/2018
Alan13/08/2018
Marcus12/07/2018
Brenden22/08/2018
Nick15/08/2018
Tim16/07/2018
Brian20/08/2018
Wisla3/09/2018
Tom26/08/2018
Andrew5/09/2018
Thomas10/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 RangeCount of Participants
04/07/2018 - 18/07/20184
19/07/2018 - 01/08/20180
02/08/2018 - 16/08/20182
17/08/2018 - 31/08/20186
01/09/2018 - 15/09/20183

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

 

 

 

 

 

 

1 Solution

Accepted Solutions
pradeepreddy
Valued Contributor II

Re: Group data in to Date Ranges based on a date column

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;
3 Replies
pradeepreddy
Valued Contributor II

Re: Group data in to Date Ranges based on a date column

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
Contributor

Re: Group data in to Date Ranges based on a date column

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/20181
02/08/2018 - 16/08/20181
04/07/2018 - 18/07/20182
17/08/2018 - 31/08/20182
19/07/2018 - 01/08/20180
-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
Contributor

Re: Group data in to Date Ranges based on a date column

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
Community Browser