Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a list of absences that I want to report on. Rather than show each date I want to show the date in blocks. Please see my data below:
Joe 01/04/14
Joe 02/04/14
Joe 03/04/14
Joe 15/07/14
Joe 16/07/14
Joe 12/08/14
Bill 01/07/14
Bill 02/07/14
Bill 20/11/14
Bill 21/11/14
Bill 15/12/15
I want to output :
Joe 01/04/14 - 03/04/14
Joe 15/07/14 - 16/07/14
Joe 12/08/14 - 12/08/14
Bill 01/07/14 - 02/07/14
Bill 20/11/14 - 21/11/14
Bill 15/12/14 - 15/12/14
Any thoughts?
Thanks
Phil
Something like that? Please find attached
do you want data in this way .
Please see below qvw.
thanks
BKC
T1:
Load Employee,Date#(Date,'DD/MM/YY') As Date Inline [
Employee,Date
Joe,01/04/14
Joe,02/04/14
Joe,03/04/14
Joe,15/07/14
Joe,16/07/14
Joe,12/08/14
Bill,01/07/14
Bill,02/07/14
Bill,20/11/14
Bill,21/11/14
Bill,15/12/15 ];
T2:
Load *,If(Employee<>Peek(Employee),AutoNumber(Employee&Date),If(Date=Peek(Date)+1,Peek(Grp),AutoNumber(Employee&Date))) As Grp Resident T1 Order By Employee,Date;
Final:
Load Employee,Date(Min(Date),'DD/MM/YY') & ' - ' & Date(Max(Date),'DD/MM/YY') Resident T2 Group By Employee,Grp;
Drop Table T1,T2;
I have simplified my table for the example, I need it to be in a chart expression.
Is this possible?
Phil
Your example is missing
Check this app
I mean, my table in my real script is huge with several joins etc, so I do not want to change my whole script just to output a table.
If I can do it as an expression that would be better.
Phil
Hey,
Can you please explain your script?
I think you have to create a group like below in script. Then you can find min and max in Chart using group
Load*,If(Employee<>Peek(Employee),AutoNumber(Employee&Date),If(Date=Peek(Date)+1,Peek(Grp),AutoNumber(Employee&Date)))As Grp Resident T1 Order By Employee,Date;