Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
philmywallet
New Contributor III

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
Valued Contributor II

Re: Date in Blocks

Something like that?  Please find attached

balkumarchandel
Valued Contributor II

Re: Date in Blocks

do you want data in this way .

Please see below qvw.

thanks

BKC

anbu1984
Honored Contributor III

Re: Date in Blocks

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;

philmywallet
New Contributor III

Re: Date in Blocks

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

Is this possible?

Phil

anbu1984
Honored Contributor III

Re: Date in Blocks

Your example is missing

anbu1984
Honored Contributor III

Re: Date in Blocks

Check this app

philmywallet
New Contributor III

Re: Date in Blocks

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
Contributor III

Re: Date in Blocks

Hey,

Can you please explain your script?

anbu1984
Honored Contributor III

Re: Date in Blocks

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;

Community Browser