Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Date in Blocks

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

13 Replies
danieloberbilli
Specialist II
Specialist II

Something like that?  Please find attached

Anonymous
Not applicable
Author

do you want data in this way .

Please see below qvw.

thanks

BKC

anbu1984
Master III
Master III

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;

Anonymous
Not applicable
Author

I have simplified my table for the example, I need it to be in a chart expression.

Is this possible?

Phil

anbu1984
Master III
Master III

Your example is missing

anbu1984
Master III
Master III

Check this app

Anonymous
Not applicable
Author

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

ankitaag
Partner - Creator III
Partner - Creator III

Hey,

Can you please explain your script?

anbu1984
Master III
Master III

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;