topic R in QlikView

R

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!!!

Re: R

sunny_talwar — Tue, 07 Mar 2017 17:09:39 GMT

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)

Re: R

maxgro — Tue, 07 Mar 2017 17:24:23 GMT

script

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)

Re: R

MarcoWedel — Tue, 07 Mar 2017 22:56:55 GMT

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

Re: R

sasiparupudi1 — Wed, 08 Mar 2017 11:48:07 GMT

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;

Re: R

Anonymous — Mon, 13 Mar 2017 14:54:02 GMT

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