topic Re: Date in Blocks in QlikView

Date in Blocks

Anonymous — Fri, 24 Apr 2015 06:45:14 GMT

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

Re: Date in Blocks

danieloberbilli — Fri, 24 Apr 2015 06:59:52 GMT

Something like that? Please find attached

Re: Date in Blocks

Anonymous — Fri, 24 Apr 2015 07:07:25 GMT

do you want data in this way .

Please see below qvw.

thanks

BKC

Re: Date in Blocks

anbu1984 — Fri, 24 Apr 2015 07:07:32 GMT

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;

Re: Date in Blocks

Anonymous — Fri, 24 Apr 2015 07:11:07 GMT

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

Is this possible?

Phil

Re: Date in Blocks

anbu1984 — Fri, 24 Apr 2015 07:12:59 GMT

Your example is missing

Re: Date in Blocks

anbu1984 — Fri, 24 Apr 2015 07:16:11 GMT

Check this app

Re: Date in Blocks

Anonymous — Fri, 24 Apr 2015 07:18:00 GMT

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

Re: Date in Blocks

ankitaag — Fri, 24 Apr 2015 07:24:00 GMT

Hey,

Can you please explain your script?

Re: Date in Blocks

anbu1984 — Fri, 24 Apr 2015 07:34:02 GMT

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;

Re: Date in Blocks

anbu1984 — Fri, 24 Apr 2015 07:41:24 GMT

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;

If(Employee<>Peek(Employee),AutoNumber(Employee&Date) If you encounter employee first time, then generate autonumber using combination of Employee&Date

If(Date=Peek(Date)+1,Peek(Grp) Else If Employee is already present, then check if previous date + one day is equal to current row date, if yes then use autonumber generated in previous row to indicate it belongs to same group

Else generate new Autonumber using AutoNumber(Employee&Date)

Re: Date in Blocks

ankitaag — Fri, 24 Apr 2015 07:58:59 GMT

But do you think it's creating the same group?

ex-

Joe,01/04/14

Joe,02/04/14

it is creating 1 for the 1st and 2 for the 2nd.

Shouldn't they belong to the same group?

Re: Date in Blocks

anbu1984 — Fri, 24 Apr 2015 08:21:02 GMT

If data is properly ordered, then you will get one for both these rows.

You can run the above script for Tables T1 and T2 and check group values

Re: Date in Blocks

ankitaag — Fri, 24 Apr 2015 08:54:45 GMT

That's what I did..

I wrote exit script; before Final table and checked that I m getting different group nos for all