R

[Anonymous]

Hello everybody!

I have a simple question (I hope ).

I woul like to have a rank of hours like

Hour	In	Out
9-10	2	3
11-12	2	5
13-14	2	8
14-15	1	0

So, my set of data´s is very simple, I have for each ID, a TimeStamp and a chart with In or Out as:

ID	Date	In/out
1	17/01/2017 09:00:00	IN
2	17/01/2017 09:40:00	IN
3	17/01/2017 09:41:00	OUT
4	17/01/2017 09:45;00	OUT
5	17/01/2017 09:50:00	OUT

Could anyone tell my how to do it?

Thank you very much!!!

[sunny_talwar · Accepted solution]

I guess extract hour from your Date field

Hour(Date) as Hour

and then use this as a dimension

Hour

Expression

Count({<[In/Out] = {'IN'}>}ID)

Count({<[In/Out] = {'OUT'}>}ID)

[maxgro]

script

W:

LOAD

  ID,

    Alt(Timestamp#(Date, 'DD/MM/YYYY hh:mm:ss[.fff]'), Timestamp#(Date, 'DD/MM/YYYY hh:mm;ss[.fff]')) as Date,

    [In/out]

FROM

[https://community.qlik.com/thread/252420]

(html, codepage is 1252, embedded labels, table is @2);

chart (pivot)

calculated dimensions

    =Dual(

    SubField(Class(Hour(Date), 2, 'x', 1), '<=', 1) & '-' & (SubField(Class(Hour(Date), 2, 'x', 1), '< ', 2)-1),

    Class(Hour(Date), 2, 'x', 1)

    )

dimension

In/Out

expression

count(ID)

[MarcoWedel]

Hi,

another solution could be:

tabSampleData:
LOAD *,
    Time#(Time(Frac(Date),'hh:mm:ss'),'hh:mm:ss') as Time;
LOAD RecNo() as ID,
    Timestamp#(Timestamp(Now()-Rand()*7,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') as Date,
    Pick(Ceil(Rand()*2),'In','Out') as [In/Out]
AutoGenerate 30;

tabTime:
LOAD *,
    Second(Time) as Second,
    Minute(Time) as Minute,
    Hour(Time) as Hour2,
    Dual(Hour(Time)&'-'&(Hour(Time)+1),Hour(Time)) as Hour;
LOAD Time(Time#(RecNo()-1,'s'),'hh:mm:ss') as Time
AutoGenerate 86400;

hope this helps

regards

Marco

[sasiparupudi1]

May be Like this?

[Time Table]:

Load

    If(Mod(RecNo(), 2) , RecNo())as FromTime,

    If(Mod(RecNo(), 2),RecNo()+1) as ToTime

Autogenerate(24);

T4:

Load

    ID,

    Date(Floor(Timestamp#(Date,'DD/MM/YYYY hh:mm:ss'))) as RecordDate,

    Time(Frac(Timestamp#(Date,'DD/MM/YYYY hh:mm:ss')),'hh:mm')  as RecordTime,

    hour(Frac(Timestamp#(Date,'DD/MM/YYYY hh:mm:ss'))) as RecordHour,

    [In/out];

Load * Inline

[

ID,Date,In/out

1,17/01/2017 09:00:00,IN

2,17/01/2017 09:40:00,IN

3,17/01/2017 09:41:00,OUT

4,17/01/2017 09:45:00,OUT

5,17/01/2017 09:50:00,OUT

];

Left Join

IntervalMatch(RecordHour)

Load  FromTime,ToTime Resident [Time Table];

Drop Table [Time Table];

Fact:

NoConcatenate Load

    ID,

    RecordDate,

    RecordTime,

    RecordHour,

    [In/out],

    FromTime,

    ToTime,

    FromTime&'-'&ToTime as TimeClass

Resident T4;

Drop Table T4;

[Anonymous]

Thank you Sunny, it worked very well !!!!