Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone. Consider the following table:
LOAD * INLINE [
Date, CustomerID
01/01/2013, 1
01/01/2013, 2
01/01/2013, 3
01/01/2013, 4
02/01/2013, 1
02/01/2013, 2
02/01/2013, 3
02/01/2013, 5
03/01/2013, 1
03/01/2013, 2
03/01/2013, 3
03/01/2013, 5
03/01/2013, 6
];
I would like to work out what the start and end dates are of each Customer. So for CustomerID 1 his start date would be 01/01/2013 and end date 03/01/2013 where as CustomerID 5 has a start date of 02/01/2013 and end date 03/01/2013 and so on. How can I calculate this?
Thank you.
Set DateFormat= 'MM/DD/YYYY';
INPUT:
LOAD * INLINE [
Date, CustomerID
01/01/2013, 1
01/01/2013, 2
01/01/2013, 3
01/01/2013, 4
02/01/2013, 1
02/01/2013, 2
02/01/2013, 3
02/01/2013, 5
03/01/2013, 1
03/01/2013, 2
03/01/2013, 3
03/01/2013, 5
03/01/2013, 6
];
LOAD Date(Min(Date)) as StartDate, Date(Max(Date)) as EndDate, CustomerID
RESIDENT INPUT
GROUP BY CustomerID;
DROP TABLE INPUT;
Set DateFormat= 'MM/DD/YYYY';
INPUT:
LOAD * INLINE [
Date, CustomerID
01/01/2013, 1
01/01/2013, 2
01/01/2013, 3
01/01/2013, 4
02/01/2013, 1
02/01/2013, 2
02/01/2013, 3
02/01/2013, 5
03/01/2013, 1
03/01/2013, 2
03/01/2013, 3
03/01/2013, 5
03/01/2013, 6
];
LOAD Date(Min(Date)) as StartDate, Date(Max(Date)) as EndDate, CustomerID
RESIDENT INPUT
GROUP BY CustomerID;
DROP TABLE INPUT;
Data:
load Date,
CustomerId
From table:
Left join(Data)
Load date(min(Date)) as StartDate,
date(max(Date)) as EndDate,
customerId
Resident Data
Group by CustomerId:
Hi ,
You can try in Expression as well
1)
aggr(max(date(Date2,'DD/MM/YYYY')),CustomerID)
2)
aggr(Min(date(Date2,'DD/MM/YYYY')),CustomerID)
On front end
Create straight table
Dimension:
CustomerId
Expression:
1) Start Date
FirstSortedValue(Date, Date)
2) End Date
FirstSortedValue(Date, -Date)
Kushal,
why shouldn't we use Min(Date) and Max(Date) as expressions also on the front end side?
Yes we can use, do we have any advantage of min and max over firstsortedvalue
Maybe only a couple:
- Max / Min could perform better, since there is no need to sort the Dates.
- No need to thinking about using DISTINCT qualifier in FirstSortedValue
- Potentially even get rid of an additional Date() format function when using Max / Min
Thanks swuehl, this worked.